--------------------------------------------------------
--  DDL for Package CA_BLACK_LIST_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_BLACK_LIST_TAPI" 
IS
type CA_BLACK_LIST_tapi_rec
IS
  record
  (
    REC_ID CA_BLACK_LIST.REC_ID%type ,
    FU_REC_ID CA_BLACK_LIST.FU_REC_ID%type ,
    USER_ID CA_BLACK_LIST.USER_ID%type ,
    DATELINE CA_BLACK_LIST.DATELINE%type ,
    FU_ID CA_BLACK_LIST.FU_ID%type );
type CA_BLACK_LIST_tapi_tab
IS
  TABLE OF CA_BLACK_LIST_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_FU_REC_ID IN CA_BLACK_LIST.FU_REC_ID%type DEFAULT NULL ,
      p_USER_ID   IN CA_BLACK_LIST.USER_ID%type DEFAULT NULL ,
      p_FU_ID     IN CA_BLACK_LIST.FU_ID%type DEFAULT NULL,
      OP_CODE  OUT number,
      OP_MSG OUT CA_ERR_CODE.ERR_MSG%TYPE );
  -- update
  PROCEDURE upd(
      p_REC_ID    IN CA_BLACK_LIST.REC_ID%type ,
      p_FU_REC_ID IN CA_BLACK_LIST.FU_REC_ID%type DEFAULT NULL ,
      p_USER_ID   IN CA_BLACK_LIST.USER_ID%type DEFAULT NULL ,
      p_DATELINE  IN CA_BLACK_LIST.DATELINE%type DEFAULT NULL ,
      p_FU_ID     IN CA_BLACK_LIST.FU_ID%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_REC_ID IN CA_BLACK_LIST.REC_ID%type );
      
 FUNCTION GET_BLACK_LIST(
	IP_USER_ID IN 	COMMON_USER.LOGIN_ID%TYPE,
	IP_FU_ID IN MOBILE_APP.APP_MODEL_ID%TYPE
)
RETURN VARCHAR;

END CA_BLACK_LIST_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_CART_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_CART_TAPI" 
IS
type CA_CART_tapi_rec
IS
  record
  (
    REC_ID CA_CART.REC_ID%type ,
    USER_ID CA_CART.USER_ID%type ,
    GOODS_NUMBER CA_CART.GOODS_NUMBER%type ,
    GOODS_NAME CA_CART.GOODS_NAME%type ,
    GOODS_ID CA_CART.GOODS_ID%type );
type CA_CART_tapi_tab
IS
  TABLE OF CA_CART_tapi_rec;
  -- insert
  PROCEDURE ins(
    ip_data in nvarchar2, op_ins_code out number
       );
  -- update
  PROCEDURE upd(
      p_REC_ID       IN CA_CART.REC_ID%type ,
      p_USER_ID      IN CA_CART.USER_ID%type ,
      p_GOODS_NUMBER IN CA_CART.GOODS_NUMBER%type DEFAULT NULL ,
      p_GOODS_NAME   IN CA_CART.GOODS_NAME%type DEFAULT NULL ,
      p_GOODS_ID     IN CA_CART.GOODS_ID%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_REC_ID IN CA_CART.REC_ID%type );
  --获取指定用户的购物车
  PROCEDURE SEL_CART_BY_USERID(
    IP_USER_ID IN NVARCHAR2,
    OP_SEL_RESULT OUT SYS_REFCURSOR,
    OP_SEL_CODE OUT NUMBER
  );
  --清空购物车
  PROCEDURE CLEAR_CART(
    IP_USER_ID IN NVARCHAR2,
    OP_OPERATE_CODE OUT NUMBER
  );
END CA_CART_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_DB_ANAL
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_DB_ANAL" 
IS

    --自定义对象类型
    type CA_RUNTIME_ANAL_REC
    IS
      record
      (
        OBJECT_NAME CA_RUNTIME_ANAL.OBJECT_NAME%type ,
        RECORD_ID CA_RUNTIME_ANAL.RECORD_ID%type ,
        COUNT_TIME CA_RUNTIME_ANAL.COUNT_TIME%type ,
        OBJECT_TYPE CA_RUNTIME_ANAL.OBJECT_TYPE%type ,
        AVER_TIME CA_RUNTIME_ANAL.AVER_TIME%type ,
        EXE_TIMES CA_RUNTIME_ANAL.EXE_TIMES%type
      );
      
  --相当于创建一个数组
  type CA_RUNTIME_ANAL_TAB
  IS
  TABLE OF CA_RUNTIME_ANAL_REC;
  
  
  --增加
  PROCEDURE ins(
      p_OBJECT_NAME IN CA_RUNTIME_ANAL.OBJECT_NAME%type DEFAULT NULL ,
     -- p_RECORD_ID   IN CA_RUNTIME_ANAL.RECORD_ID%type ,
      p_COUNT_TIME  IN CA_RUNTIME_ANAL.COUNT_TIME%type ,
      p_OBJECT_TYPE IN CA_RUNTIME_ANAL.OBJECT_TYPE%type ,
      p_AVER_TIME   IN CA_RUNTIME_ANAL.AVER_TIME%type DEFAULT NULL ,
      p_EXE_TIMES   IN CA_RUNTIME_ANAL.EXE_TIMES%type DEFAULT NULL );
 
  -- 更新
  PROCEDURE upd(
      p_OBJECT_NAME IN CA_RUNTIME_ANAL.OBJECT_NAME%type DEFAULT NULL ,
      p_RECORD_ID   IN CA_RUNTIME_ANAL.RECORD_ID%type ,
      p_COUNT_TIME  IN CA_RUNTIME_ANAL.COUNT_TIME%type ,
      p_OBJECT_TYPE IN CA_RUNTIME_ANAL.OBJECT_TYPE%type ,
      p_AVER_TIME   IN CA_RUNTIME_ANAL.AVER_TIME%type DEFAULT NULL ,
      p_EXE_TIMES   IN CA_RUNTIME_ANAL.EXE_TIMES%type DEFAULT NULL );
  -- 删除全部
  PROCEDURE del;
  --删除指定记录
  PROCEDURE DEL_BY_ID(
    IP_RECORD_ID IN CA_RUNTIME_ANAL.RECORD_ID%type 
  );
END CA_DB_ANAL;

/
--------------------------------------------------------
--  DDL for Package CA_FOOD_CATEGORY_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_FOOD_CATEGORY_TAPI" 
IS
type CA_FOOD_CATEGORY_tapi_rec
IS
  record
  (
    CATEGORY_NAME CA_FOOD_CATEGORY.CATEGORY_NAME%type ,
    CATEGORY_ID CA_FOOD_CATEGORY.CATEGORY_ID%type );
type CA_FOOD_CATEGORY_tapi_tab
IS
  TABLE OF CA_FOOD_CATEGORY_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_CATEGORY_NAME IN CA_FOOD_CATEGORY.CATEGORY_NAME%type DEFAULT NULL 
      --p_CATEGORY_ID   IN CA_FOOD_CATEGORY.CATEGORY_ID%type
      );
  -- update
  PROCEDURE upd(
      p_CATEGORY_NAME IN CA_FOOD_CATEGORY.CATEGORY_NAME%type DEFAULT NULL ,
      p_CATEGORY_ID   IN CA_FOOD_CATEGORY.CATEGORY_ID%type );
  -- delete
  PROCEDURE del(
      p_CATEGORY_ID IN CA_FOOD_CATEGORY.CATEGORY_ID%type );
       /*==============选择全部数据================================*/
  PROCEDURE SEL_ALL(
    OP_SEL_RESULT OUT SYS_REFCURSOR,
    OP_SEL_MESSAGE OUT NVARCHAR2
  );
END CA_FOOD_CATEGORY_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_FOOD_TYPE_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_FOOD_TYPE_TAPI" 
IS
type CA_FOOD_TYPE_tapi_rec
IS
  record
  (
    TYPE_NAME CA_FOOD_TYPE.TYPE_NAME%type ,
    TYPE_ID CA_FOOD_TYPE.TYPE_ID%type );
type CA_FOOD_TYPE_tapi_tab
IS
  TABLE OF CA_FOOD_TYPE_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_TYPE_NAME IN CA_FOOD_TYPE.TYPE_NAME%type DEFAULT NULL 
     -- p_TYPE_ID   IN CA_FOOD_TYPE.TYPE_ID%type 
     );
  -- update
  PROCEDURE upd(
      p_TYPE_NAME IN CA_FOOD_TYPE.TYPE_NAME%type DEFAULT NULL ,
      p_TYPE_ID   IN CA_FOOD_TYPE.TYPE_ID%type );
  -- delete
  PROCEDURE del(
      p_TYPE_ID IN CA_FOOD_TYPE.TYPE_ID%type );
      
  /*==============选择全部数据================================*/
  PROCEDURE SEL_ALL(
   OP_SEL_RESULT OUT SYS_REFCURSOR,
    OP_SEL_MESSAGE OUT NVARCHAR2
  );
END CA_FOOD_TYPE_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_MERCHANT_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_MERCHANT_TAPI" 
IS
type CA_MERCHANT_tapi_rec
IS
  record
  (
    ODER_LIST CA_MERCHANT.ODER_LIST%type ,
    LAST_MODIFY CA_MERCHANT.LAST_MODIFY%type ,
    LOGIN_ID CA_MERCHANT.LOGIN_ID%type ,
    REG_IP CA_MERCHANT.REG_IP%type ,
    REG_DATE CA_MERCHANT.REG_DATE%type ,
    CONTACT_NAME CA_MERCHANT.CONTACT_NAME%type ,
    RECORD_ID CA_MERCHANT.RECORD_ID%type ,
    CONTACT_PHONE CA_MERCHANT.CONTACT_PHONE%type ,
    MERCHANT_NAME CA_MERCHANT.MERCHANT_NAME%type ,
    NEW_ORDER CA_MERCHANT.NEW_ORDER%type ,
    ENCRYPTPASSWORD CA_MERCHANT.ENCRYPTPASSWORD%type ,
    MERCHANT_ADDRESS CA_MERCHANT.MERCHANT_ADDRESS%type );
type CA_MERCHANT_tapi_tab
IS
  TABLE OF CA_MERCHANT_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_ODER_LIST        IN CA_MERCHANT.ODER_LIST%type DEFAULT NULL ,
      p_LAST_MODIFY      IN CA_MERCHANT.LAST_MODIFY%type DEFAULT NULL ,
      p_LOGIN_ID         IN CA_MERCHANT.LOGIN_ID%type DEFAULT NULL ,
      p_REG_IP           IN CA_MERCHANT.REG_IP%type DEFAULT NULL ,
      p_REG_DATE         IN CA_MERCHANT.REG_DATE%type DEFAULT NULL ,
      p_CONTACT_NAME     IN CA_MERCHANT.CONTACT_NAME%type DEFAULT NULL ,
      p_RECORD_ID        IN CA_MERCHANT.RECORD_ID%type ,
      p_CONTACT_PHONE    IN CA_MERCHANT.CONTACT_PHONE%type DEFAULT NULL ,
      p_MERCHANT_NAME    IN CA_MERCHANT.MERCHANT_NAME%type DEFAULT NULL ,
      p_NEW_ORDER        IN CA_MERCHANT.NEW_ORDER%type DEFAULT NULL ,
      p_ENCRYPTPASSWORD  IN CA_MERCHANT.ENCRYPTPASSWORD%type DEFAULT NULL ,
      p_MERCHANT_ADDRESS IN CA_MERCHANT.MERCHANT_ADDRESS%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_ODER_LIST        IN CA_MERCHANT.ODER_LIST%type DEFAULT NULL ,
      p_LAST_MODIFY      IN CA_MERCHANT.LAST_MODIFY%type DEFAULT NULL ,
      p_LOGIN_ID         IN CA_MERCHANT.LOGIN_ID%type DEFAULT NULL ,
      p_REG_IP           IN CA_MERCHANT.REG_IP%type DEFAULT NULL ,
      p_REG_DATE         IN CA_MERCHANT.REG_DATE%type DEFAULT NULL ,
      p_CONTACT_NAME     IN CA_MERCHANT.CONTACT_NAME%type DEFAULT NULL ,
      p_RECORD_ID        IN CA_MERCHANT.RECORD_ID%type ,
      p_CONTACT_PHONE    IN CA_MERCHANT.CONTACT_PHONE%type DEFAULT NULL ,
      p_MERCHANT_NAME    IN CA_MERCHANT.MERCHANT_NAME%type DEFAULT NULL ,
      p_NEW_ORDER        IN CA_MERCHANT.NEW_ORDER%type DEFAULT NULL ,
      p_ENCRYPTPASSWORD  IN CA_MERCHANT.ENCRYPTPASSWORD%type DEFAULT NULL ,
      p_MERCHANT_ADDRESS IN CA_MERCHANT.MERCHANT_ADDRESS%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN CA_MERCHANT.RECORD_ID%type );
      
      PROCEDURE LOGIN(
          IP_USERNAME IN CA_MERCHANT.LOGIN_ID%TYPE,
          IP_PASSWORD IN CA_MERCHANT.ENCRYPTPASSWORD%TYPE,
          OP_OP_CODE OUT NUMBER,
          OP_SELECT_RESULT OUT SYS_REFCURSOR
);

--更改密码
   PROCEDURE UPD_PASSWORD(
          IP_USERNAME IN CA_MERCHANT.LOGIN_ID%TYPE,
          IP_PASSWORD IN CA_MERCHANT.ENCRYPTPASSWORD%TYPE,
          OP_OP_CODE OUT NUMBER
   );
   
   
   --获取用户最新消息
   PROCEDURE GET_LAST_MESSAGE(
       IP_USERNAME IN CA_MERCHANT.LOGIN_ID%TYPE,
       OP_NEW_PROM_COUNT OUT NUMBER       
   );
  
 
END CA_MERCHANT_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_MESSAGES_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_MESSAGES_TAPI" 
IS
type CA_MESSAGES_tapi_rec
IS
  record
  (
    READER_LIST CA_MESSAGES.READER_LIST%type ,
    REC_ID CA_MESSAGES.REC_ID%type ,
    GEN_TIME CA_MESSAGES.GEN_TIME%type ,
    READ_STATUS CA_MESSAGES.READ_STATUS%type ,
    TYPE_ID CA_MESSAGES.TYPE_ID%type ,
    RECV_ID CA_MESSAGES.RECV_ID%type ,
    TITLE CA_MESSAGES.TITLE%type ,
    FU_ID CA_MESSAGES.FU_ID%type );
type CA_MESSAGES_tapi_tab
IS
  TABLE OF CA_MESSAGES_tapi_rec;
  -- insert
  PROCEDURE ins(
    --  p_READER_LIST IN CA_MESSAGES.READER_LIST%type ,
     -- p_REC_ID      IN CA_MESSAGES.REC_ID%type ,
     -- p_GEN_TIME    IN CA_MESSAGES.GEN_TIME%type ,
    --  p_READ_STATUS IN CA_MESSAGES.READ_STATUS%type ,
      p_TYPE_ID     IN CA_MESSAGES.TYPE_ID%type ,
      p_RECV_ID     IN CA_MESSAGES.RECV_ID%type ,
      p_TITLE       IN CA_MESSAGES.TITLE%type ,
      p_FU_ID       IN CA_MESSAGES.FU_ID%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_READER_LIST IN CA_MESSAGES.READER_LIST%type ,
      p_REC_ID      IN CA_MESSAGES.REC_ID%type ,
      p_GEN_TIME    IN CA_MESSAGES.GEN_TIME%type ,
      p_READ_STATUS IN CA_MESSAGES.READ_STATUS%type ,
      p_TYPE_ID     IN CA_MESSAGES.TYPE_ID%type ,
      p_RECV_ID     IN CA_MESSAGES.RECV_ID%type ,
      p_TITLE       IN CA_MESSAGES.TITLE%type ,
      p_FU_ID       IN CA_MESSAGES.FU_ID%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_REC_ID IN CA_MESSAGES.REC_ID%type );
      
      --获取用户的最新消息
      PROCEDURE GET_LAST_MESSAGE(
       IP_USERNAME IN CA_MERCHANT.LOGIN_ID%TYPE,
       op_new_messages OUT NUMBER       
      );
      
     PROCEDURE GET_MESSAGE(
    IP_PAGE_INDEX IN NUMBER,
    IP_PAGE_SIZE IN NUMBER,
    IP_USER_ID IN COMMON_USER.LOGIN_ID%TYPE,
    OP_SEL_RESULT OUT SYS_REFCURSOR,
    OP_OP_CODE OUT NUMBER
    );

   PROCEDURE MARK_READ(
      IP_REC_ID IN NUMBER,--记录ID
      TYPE_ID IN NUMBER,--栏目ID,传入该参数是因为有的消息是存放的标记队列
      IP_USER_ID IN COMMON_USER.LOGIN_ID%TYPE,
      OP_OP_CODE OUT NUMBER--操作代码
    );

  PROCEDURE ALL_MESSAGES(
   IP_PAGE_INDEX IN NUMBER,--
   IP_PAGE_SIZE IN NUMBER,
   IP_USER_ID IN COMMON_USER.LOGIN_ID%TYPE,
   OP_SEL_RESULT OUT SYS_REFCURSOR,
   OP_OP_CODE OUT NUMBER
);

END CA_MESSAGES_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_NETWORK_PROCESS_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_NETWORK_PROCESS_TAPI" 
IS
type CA_NETWORK_PROCESS_tapi_rec
IS
  record
  (
    REC_ID CA_NETWORK_PROCESS.REC_ID%type ,
    PROCESS_STATUS_ID CA_NETWORK_PROCESS.PROCESS_STATUS_ID%type ,
    USER_ID CA_NETWORK_PROCESS.USER_ID%type ,
    COMMIT_TIME CA_NETWORK_PROCESS.COMMIT_TIME%type ,
    PROCESS_TIME CA_NETWORK_PROCESS.PROCESS_TIME%type ,
    CONTACT_TEL CA_NETWORK_PROCESS.CONTACT_TEL%type ,
    PROCESS_ANALYSIS CA_NETWORK_PROCESS.PROCESS_ANALYSIS%type ,
    CONTACT_MOBILE CA_NETWORK_PROCESS.CONTACT_MOBILE%type ,
    BREAKDOWN_DESC CA_NETWORK_PROCESS.BREAKDOWN_DESC%type ,
    BREAKDOWN_ADDRESS CA_NETWORK_PROCESS.BREAKDOWN_ADDRESS%type ,
    CONTACT_MAN CA_NETWORK_PROCESS.CONTACT_MAN%type );
type CA_NETWORK_PROCESS_tapi_tab
IS
  TABLE OF CA_NETWORK_PROCESS_tapi_rec;
  -- insert
  PROCEDURE ins(
     -- p_REC_ID            IN CA_NETWORK_PROCESS.REC_ID%type ,
    --  p_PROCESS_STATUS_ID IN CA_NETWORK_PROCESS.PROCESS_STATUS_ID%type DEFAULT NULL ,
      p_USER_ID           IN CA_NETWORK_PROCESS.USER_ID%type DEFAULT NULL ,
    --  p_COMMIT_TIME       IN CA_NETWORK_PROCESS.COMMIT_TIME%type DEFAULT NULL ,
      --p_PROCESS_TIME      IN CA_NETWORK_PROCESS.PROCESS_TIME%type ,
      p_CONTACT_TEL       IN CA_NETWORK_PROCESS.CONTACT_TEL%type DEFAULT NULL ,
     -- p_PROCESS_ANALYSIS  IN CA_NETWORK_PROCESS.PROCESS_ANALYSIS%type DEFAULT NULL ,
      p_CONTACT_MOBILE    IN CA_NETWORK_PROCESS.CONTACT_MOBILE%type DEFAULT NULL ,
      p_BREAKDOWN_DESC    IN CA_NETWORK_PROCESS.BREAKDOWN_DESC%type ,
      p_BREAKDOWN_ADDRESS IN CA_NETWORK_PROCESS.BREAKDOWN_ADDRESS%type ,
      p_CONTACT_MAN       IN CA_NETWORK_PROCESS.CONTACT_MAN%type DEFAULT NULL,
          p_BREAKDOWN_TYPE    IN CA_NETWORK_PROCESS.BREAKDOWN_TYPE%type DEFAULT NULL,
       OP_OPERATE_CODE     OUT NUMBER
      );
  -- update
  PROCEDURE upd(
      p_REC_ID            IN CA_NETWORK_PROCESS.REC_ID%type ,
      p_PROCESS_STATUS_ID IN CA_NETWORK_PROCESS.PROCESS_STATUS_ID%type DEFAULT NULL ,
      p_USER_ID           IN CA_NETWORK_PROCESS.USER_ID%type DEFAULT NULL ,
      p_COMMIT_TIME       IN CA_NETWORK_PROCESS.COMMIT_TIME%type DEFAULT NULL ,
      p_PROCESS_TIME      IN CA_NETWORK_PROCESS.PROCESS_TIME%type ,
      p_CONTACT_TEL       IN CA_NETWORK_PROCESS.CONTACT_TEL%type DEFAULT NULL ,
      p_PROCESS_ANALYSIS  IN CA_NETWORK_PROCESS.PROCESS_ANALYSIS%type DEFAULT NULL ,
      p_CONTACT_MOBILE    IN CA_NETWORK_PROCESS.CONTACT_MOBILE%type DEFAULT NULL ,
      p_BREAKDOWN_DESC    IN CA_NETWORK_PROCESS.BREAKDOWN_DESC%type ,
      p_BREAKDOWN_ADDRESS IN CA_NETWORK_PROCESS.BREAKDOWN_ADDRESS%type ,
      p_CONTACT_MAN       IN CA_NETWORK_PROCESS.CONTACT_MAN%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_REC_ID IN CA_NETWORK_PROCESS.REC_ID%type );
FUNCTION GET_PROCESS_STATUS(
	IP_STATUS_ID IN NUMBER
)
RETURN NVARCHAR2;
---分页获取数据
PROCEDURE PAGE_NETWORK(
    IP_PAGE_INDEX IN NUMBER,--索引
    IP_PAGE_SIZE  IN NUMBER,--页面大小
    IP_MERCHANT_ID IN CA_SIMPLE_FOOD.MERCHANT_ID%TYPE,--商家ID
    SELECT_RESULT OUT SYS_REFCURSOR,--查询结果
    OP_ROWCOUNT OUT NUMBER ,--数据量
	OP_CODE OUT NUMBER,---操作代码
    OP_MSG OUT NVARCHAR2
);

PROCEDURE SEL_VIEW(
IP_REC_ID IN NUMBER,
OP_SEL_RESULT OUT SYS_REFCURSOR,
OP_CODE OUT NUMBER,
OP_MSG  OUT NVARCHAR2--更新消息
);

PROCEDURE DELETE_V2(
	IP_RECID IN NUMBER,
	IP_CODE OUT NUMBER,
	OP_MSG  OUT NVARCHAR2
);

PROCEDURE MARK_STATUS(
	IP_REC_ID IN NUMBER,
	IP_STATUS_ID IN NUMBER,
	OP_CODE OUT NUMBER,
	OP_MSG OUT NVARCHAR2

);

PROCEDURE FINISH(
	IP_REC_ID  IN NUMBER,
	IP_PROCESS_MAN IN NVARCHAR2,--处理人
	IP_PROCESS_ANALYSIS CA_NETWORK_PROCESS.PROCESS_ANALYSIS%TYPE,
	OP_CODE OUT NUMBER,
	OP_MSG OUT NVARCHAR2
);

PROCEDURE SEL_VIEW_V2(
IP_REC_ID IN NUMBER,
IP_MERCHANT_TYPE IN NUMBER,
OP_SEL_RESULT OUT SYS_REFCURSOR,
OP_CODE OUT NUMBER,
OP_MSG  OUT NVARCHAR2--更新消息
);

PROCEDURE PAGE_NETWORKV2(
    IP_PAGE_INDEX IN NUMBER,--索引
    IP_PAGE_SIZE  IN NUMBER,--页面大小
    IP_MERCHANT_ID IN CA_SIMPLE_FOOD.MERCHANT_ID%TYPE,--商家ID
    SELECT_RESULT OUT SYS_REFCURSOR,--查询结果
    OP_ROWCOUNT OUT NUMBER ,--数据量
	OP_CODE OUT NUMBER,---操作代码
    OP_MSG OUT NVARCHAR2
);
END CA_NETWORK_PROCESS_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_ORDER_GOODS_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_ORDER_GOODS_TAPI" 
IS
type CA_ORDER_GOODS_tapi_rec
IS
  record
  (
    REC_ID CA_ORDER_GOODS.REC_ID%type ,
    ORDER_ID CA_ORDER_GOODS.ORDER_ID%type ,
    GOODS_NUMBER CA_ORDER_GOODS.GOODS_NUMBER%type ,
    GOODS_NAME CA_ORDER_GOODS.GOODS_NAME%type ,
    GOODS_ID CA_ORDER_GOODS.GOODS_ID%type );
type CA_ORDER_GOODS_tapi_tab
IS
  TABLE OF CA_ORDER_GOODS_tapi_rec;
  -- insert
 PROCEDURE ins(
    --p_REC_ID       IN CA_ORDER_GOODS.REC_ID%type ,
    p_ORDER_ID     IN CA_ORDER_GOODS.ORDER_ID%type DEFAULT NULL ,
    p_GOODS_NUMBER IN CA_ORDER_GOODS.GOODS_NUMBER%type DEFAULT NULL ,
    p_GOODS_NAME   IN CA_ORDER_GOODS.GOODS_NAME%type DEFAULT NULL ,
    p_GOODS_ID     IN CA_ORDER_GOODS.GOODS_ID%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_REC_ID       IN CA_ORDER_GOODS.REC_ID%type ,
      p_ORDER_ID     IN CA_ORDER_GOODS.ORDER_ID%type DEFAULT NULL ,
      p_GOODS_NUMBER IN CA_ORDER_GOODS.GOODS_NUMBER%type DEFAULT NULL ,
      p_GOODS_NAME   IN CA_ORDER_GOODS.GOODS_NAME%type DEFAULT NULL ,
      p_GOODS_ID     IN CA_ORDER_GOODS.GOODS_ID%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_REC_ID IN CA_ORDER_GOODS.REC_ID%type );
END CA_ORDER_GOODS_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_ORDER_INFO_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_ORDER_INFO_TAPI" 
IS
type CA_ORDER_INFO_tapi_rec
IS
  record
  (
    USER_MARK CA_ORDER_INFO.USER_MARK%type ,
    ORDER_STATUS CA_ORDER_INFO.ORDER_STATUS%type ,
    GOODS_AMOUNT CA_ORDER_INFO.GOODS_AMOUNT%type ,
    USER_ID CA_ORDER_INFO.USER_ID%type ,
    MERCHANT_NOTE CA_ORDER_INFO.MERCHANT_NOTE%type ,
    SHIPPING_FEE CA_ORDER_INFO.SHIPPING_FEE%type ,
    MERCHANT_MARK CA_ORDER_INFO.MERCHANT_MARK%type ,
    DELIVERY_ADDRESS CA_ORDER_INFO.DELIVERY_ADDRESS%type ,
    CONFIRM_TIME CA_ORDER_INFO.CONFIRM_TIME%type ,
    ADD_TIME CA_ORDER_INFO.ADD_TIME%type ,
    USER_NOTE CA_ORDER_INFO.USER_NOTE%type ,
    ORDER_ID CA_ORDER_INFO.ORDER_ID%type ,
    MOBILE CA_ORDER_INFO.MOBILE%type ,
    TEL CA_ORDER_INFO.TEL%type ,
    GOODS_ID CA_ORDER_INFO.GOODS_ID%type ,
    MERCHANT_ID CA_ORDER_INFO.MERCHANT_ID%type ,
    CONSIGNEE CA_ORDER_INFO.CONSIGNEE%type );
type CA_ORDER_INFO_tapi_tab
IS
  TABLE OF CA_ORDER_INFO_tapi_rec;
  
  /*========================自定义数据类型=========================*/
  type order_info_rec
  is
  record
  (
  amount ca_order_info.goods_amount%type,
  userid common_user.login_id%type,
  delivery_address ca_order_info.delivery_address%type,
  mobile ca_order_info.mobile%type,
  tel ca_order_info.tel%type,
  goods_id  ca_simple_food.record_id%type,
  merchat_id ca_merchant.login_id%type,
  consignee ca_order_info.consignee%type,
  goods_number ca_order_goods.goods_number%type,
  goods_name ca_simple_food.food_name%type
  );
  type order_info_tab is table of order_info_rec;
  
  -- insert
  PROCEDURE ins(
   p_USER_MARK        IN CA_ORDER_INFO.USER_MARK%type DEFAULT NULL ,
    p_ORDER_STATUS     IN CA_ORDER_INFO.ORDER_STATUS%type DEFAULT NULL ,
    p_GOODS_AMOUNT     IN CA_ORDER_INFO.GOODS_AMOUNT%type DEFAULT NULL ,
    p_USER_ID          IN CA_ORDER_INFO.USER_ID%type DEFAULT NULL ,
    p_MERCHANT_NOTE    IN CA_ORDER_INFO.MERCHANT_NOTE%type DEFAULT NULL ,
    p_SHIPPING_FEE     IN CA_ORDER_INFO.SHIPPING_FEE%type DEFAULT NULL ,
    p_MERCHANT_MARK    IN CA_ORDER_INFO.MERCHANT_MARK%type DEFAULT NULL ,
    p_DELIVERY_ADDRESS IN CA_ORDER_INFO.DELIVERY_ADDRESS%type ,
    p_CONFIRM_TIME     IN CA_ORDER_INFO.CONFIRM_TIME%type DEFAULT NULL ,
    p_ADD_TIME         IN CA_ORDER_INFO.ADD_TIME%type DEFAULT NULL ,
    p_USER_NOTE        IN CA_ORDER_INFO.USER_NOTE%type DEFAULT NULL ,
  --  p_ORDER_ID         IN CA_ORDER_INFO.ORDER_ID%type ,
    p_MOBILE           IN CA_ORDER_INFO.MOBILE%type ,
    p_TEL              IN CA_ORDER_INFO.TEL%type DEFAULT NULL ,
    p_GOODS_ID         IN CA_ORDER_INFO.GOODS_ID%type DEFAULT NULL ,
    p_MERCHANT_ID      IN CA_ORDER_INFO.MERCHANT_ID%type ,
    p_CONSIGNEE        IN CA_ORDER_INFO.CONSIGNEE%type DEFAULT NULL,
    op_ins_code out number
      );
  -- update
  PROCEDURE upd(
      p_USER_MARK        IN CA_ORDER_INFO.USER_MARK%type DEFAULT NULL ,
      p_ORDER_STATUS     IN CA_ORDER_INFO.ORDER_STATUS%type DEFAULT NULL ,
      p_GOODS_AMOUNT     IN CA_ORDER_INFO.GOODS_AMOUNT%type DEFAULT NULL ,
      p_USER_ID          IN CA_ORDER_INFO.USER_ID%type DEFAULT NULL ,
      p_MERCHANT_NOTE    IN CA_ORDER_INFO.MERCHANT_NOTE%type DEFAULT NULL ,
      p_SHIPPING_FEE     IN CA_ORDER_INFO.SHIPPING_FEE%type DEFAULT NULL ,
      p_MERCHANT_MARK    IN CA_ORDER_INFO.MERCHANT_MARK%type DEFAULT NULL ,
      p_DELIVERY_ADDRESS IN CA_ORDER_INFO.DELIVERY_ADDRESS%type ,
      p_CONFIRM_TIME     IN CA_ORDER_INFO.CONFIRM_TIME%type DEFAULT NULL ,
      p_ADD_TIME         IN CA_ORDER_INFO.ADD_TIME%type DEFAULT NULL ,
      p_USER_NOTE        IN CA_ORDER_INFO.USER_NOTE%type DEFAULT NULL ,
      p_ORDER_ID         IN CA_ORDER_INFO.ORDER_ID%type ,
      p_MOBILE           IN CA_ORDER_INFO.MOBILE%type ,
      p_TEL              IN CA_ORDER_INFO.TEL%type DEFAULT NULL ,
      p_GOODS_ID         IN CA_ORDER_INFO.GOODS_ID%type DEFAULT NULL ,
      p_MERCHANT_ID      IN CA_ORDER_INFO.MERCHANT_ID%type ,
      p_CONSIGNEE        IN CA_ORDER_INFO.CONSIGNEE%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_ORDER_ID IN CA_ORDER_INFO.ORDER_ID%type );
      /*增加订单v2*/
  procedure ins_v2(ip_data in nvarchar2, op_ins_code out number );
  
  --查询用户订单
  PROCEDURE SEL_ORDER_BY_USER(
    IP_PAGE_INDEX IN NUMBER,
    IP_PAGE_SIZE IN NUMBER,
    IP_USER_ID IN NVARCHAR2,
    OP_SELECT_RESULT OUT SYS_REFCURSOR,
    OP_OPERATE_CODE OUT NUMBER
  );
  
  
  --查询商家订单信息
   PROCEDURE SEL_MERCHANT_ORDER_LIST(
    IP_PAGE_INDEX IN NUMBER,
    IP_PAGE_SIZE IN NUMBER,
    IP_USER_ID IN NVARCHAR2,
    OP_SELECT_RESULT OUT SYS_REFCURSOR,
    OP_OPERATE_CODE OUT NUMBER
  );
  
  FUNCTION GET_ORDER_STATUS(ip_status_id in number) RETURN NVARCHAR2;
  
  --WEB端订单管理
   PROCEDURE WEB_SEL_ORDER_BY_USER(
    IP_PAGE_INDEX IN NUMBER,
    IP_PAGE_SIZE IN NUMBER,
    IP_USER_ID IN NVARCHAR2,
    OP_SELECT_RESULT OUT SYS_REFCURSOR,
    OP_ROW_COUNT OUT NUMBER,
    OP_OPERATE_CODE OUT NUMBER
  );
  
END CA_ORDER_INFO_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_PKG_COMMON
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_PKG_COMMON" IS
  --将DATE转换为Unix时间戳
  FUNCTION DATE_2_TIMESTAMP(IP_DATE IN DATE) RETURN NUMBER;
  /*===========================根据经纬度计算两点距离=======================*/
  FUNCTION GetDistance(lat1 number,
                       lng1 number,
                       lat2 number,
                       lng2 number) 
  RETURN NUMBER ;
   FUNCTION RAD(d number) RETURN NUMBER;
  
END CA_PKG_COMMON;

/
--------------------------------------------------------
--  DDL for Package CA_PKG_COMMON_USER
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_PKG_COMMON_USER" 
IS
type CA_PKG_COMMON_USER_REC
IS
  record
  (
    LOGIN_ID COMMON_USER.LOGIN_ID%type ,
    REGSTATUS COMMON_USER.REGSTATUS%type ,
    CHANNEL_ID COMMON_USER.CHANNEL_ID%type ,
    GROUPID COMMON_USER.GROUPID%type ,
    USER_APP_ID COMMON_USER.USER_APP_ID%type ,
    USER_TYPE COMMON_USER.USER_TYPE%type ,
    STATUS COMMON_USER.STATUS%type ,
    ENCRYPTPASSWORD COMMON_USER.ENCRYPTPASSWORD%type ,
    NICKNAME COMMON_USER.NICKNAME%type ,
    USER_MOBILE_TYPE COMMON_USER.USER_MOBILE_TYPE%type ,
    ALLOWADMINCP COMMON_USER.ALLOWADMINCP%type ,
    RECORD_ID COMMON_USER.RECORD_ID%type ,
    IDNUMBER COMMON_USER.IDNUMBER%type ,
    USER_TAGS COMMON_USER.USER_TAGS%type ,
    NEWPROMPT COMMON_USER.NEWPROMPT%type ,
    NEWPM COMMON_USER.NEWPM%type );
type CA_PKG_COMMON_USER_TAB
IS
  TABLE OF CA_PKG_COMMON_USER_REC;
  -- insert
  PROCEDURE ins(
      p_LOGIN_ID         IN COMMON_USER.LOGIN_ID%type ,
      p_REGSTATUS        IN COMMON_USER.REGSTATUS%type DEFAULT NULL ,
      p_CHANNEL_ID       IN COMMON_USER.CHANNEL_ID%type DEFAULT NULL ,
      p_GROUPID          IN COMMON_USER.GROUPID%type DEFAULT NULL ,
      p_USER_APP_ID      IN COMMON_USER.USER_APP_ID%type DEFAULT NULL ,
      p_USER_TYPE        IN COMMON_USER.USER_TYPE%type DEFAULT NULL ,
      p_STATUS           IN COMMON_USER.STATUS%type DEFAULT NULL ,
      p_ENCRYPTPASSWORD  IN COMMON_USER.ENCRYPTPASSWORD%type DEFAULT NULL ,
      p_NICKNAME         IN COMMON_USER.NICKNAME%type DEFAULT NULL ,
      p_USER_MOBILE_TYPE IN COMMON_USER.USER_MOBILE_TYPE%type DEFAULT NULL ,
      p_ALLOWADMINCP     IN COMMON_USER.ALLOWADMINCP%type DEFAULT NULL ,
      --p_RECORD_ID        IN COMMON_USER.RECORD_ID%type ,
      p_IDNUMBER         IN COMMON_USER.IDNUMBER%type DEFAULT NULL ,
      p_USER_TAGS        IN COMMON_USER.USER_TAGS%type DEFAULT NULL ,
      p_NEWPROMPT        IN COMMON_USER.NEWPROMPT%type DEFAULT NULL ,
      p_NEWPM            IN COMMON_USER.NEWPM%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_LOGIN_ID         IN COMMON_USER.LOGIN_ID%type ,
      p_REGSTATUS        IN COMMON_USER.REGSTATUS%type DEFAULT NULL ,
      p_CHANNEL_ID       IN COMMON_USER.CHANNEL_ID%type DEFAULT NULL ,
      p_GROUPID          IN COMMON_USER.GROUPID%type DEFAULT NULL ,
      p_USER_APP_ID      IN COMMON_USER.USER_APP_ID%type DEFAULT NULL ,
      p_USER_TYPE        IN COMMON_USER.USER_TYPE%type DEFAULT NULL ,
      p_STATUS           IN COMMON_USER.STATUS%type DEFAULT NULL ,
      p_ENCRYPTPASSWORD  IN COMMON_USER.ENCRYPTPASSWORD%type DEFAULT NULL ,
      p_NICKNAME         IN COMMON_USER.NICKNAME%type DEFAULT NULL ,
      p_USER_MOBILE_TYPE IN COMMON_USER.USER_MOBILE_TYPE%type DEFAULT NULL ,
      p_ALLOWADMINCP     IN COMMON_USER.ALLOWADMINCP%type DEFAULT NULL ,
      p_RECORD_ID        IN COMMON_USER.RECORD_ID%type ,
      p_IDNUMBER         IN COMMON_USER.IDNUMBER%type DEFAULT NULL ,
      p_USER_TAGS        IN COMMON_USER.USER_TAGS%type DEFAULT NULL ,
      p_NEWPROMPT        IN COMMON_USER.NEWPROMPT%type DEFAULT NULL ,
      p_NEWPM            IN COMMON_USER.NEWPM%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_LOGIN_ID IN COMMON_USER.LOGIN_ID%type );
      
      
/*=========================================查找用户====================================================*/     
  PROCEDURE SEL_BY_LOGINUD (
    IP_LOGIN_ID IN COMMON_USER.LOGIN_ID%type ,
    OP_OPERATE_CODE OUT NUMBER,
    OP_SEL_RESULT OUT SYS_REFCURSOR
  );
 /*====================================更新用户信息====================================================*/ 
  PROCEDURE UPDATE_INFO(
    IP_LOGIN_ID         IN COMMON_USER.LOGIN_ID%type ,
    IP_IDNUMBER         IN COMMON_USER.IDNUMBER%type DEFAULT NULL ,
    IP_USER_TYPE        IN COMMON_USER.USER_TYPE%type DEFAULT NULL ,
    IP_TRUE_NAME       IN COMMON_USER_PROFILE.TRUENAME%TYPE,
    OP_OPERATE_CODE OUT NUMBER
  );
  PROCEDURE UPLOADUSERHEAD(
	IP_USER_ID IN COMMON_USER.LOGIN_ID%TYPE,
	IP_USERHEAD IN COMMON_USER_PROFILE.USERHEAD%TYPE,
	OP_CODE OUT NUMBER
);
  PROCEDURE updatePassword (
    IP_LOGIN_ID IN COMMON_USER.LOGIN_ID%type ,
    OP_PASSWROD IN COMMON_USER.ENCRYPTPASSWORD%TYPE,
	OP_NEW_PASSWORD IN COMMON_USER.ENCRYPTPASSWORD%TYPE,
    OP_CODE OUT NUMBER
 );
 
 
 
 
PROCEDURE UPDATEUSERPROFILE(
	IP_LOGIN_ID IN COMMON_USER.LOGIN_ID%TYPE,
	IP_BIRTHYEAR IN COMMON_USER_PROFILE.BIRTHYEAR%TYPE,
	IP_BIRTHMONTH IN COMMON_USER_PROFILE.BIRTHMONTH%TYPE,
	IP_BIRTHDAY IN COMMON_USER_PROFILE.BIRTHDAY%TYPE,
	IP_NICKNAME IN COMMON_USER.NICKNAME%TYPE,
	IP_SIGNATURE IN COMMON_USER_PROFILE.SIGNATURE%TYPE,
	IP_GENDER IN COMMON_USER_PROFILE.GENDER%TYPE,
	IP_ADDRESS IN COMMON_USER_PROFILE.ADDRESS%TYPE,
	IP_MAJORNAME IN COMMON_USER_PROFILE.MAJORID%TYPE,
	IP_CONSTELLATION IN COMMON_USER_PROFILE.CONSTELLATION%TYPE,
	IP_AGE IN COMMON_USER_PROFILE.AGE%TYPE,
	OP_CODE OUT NUMBER 
);
END CA_PKG_COMMON_USER;

/
--------------------------------------------------------
--  DDL for Package CA_PKG_DETAIL_FOOD
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_PKG_DETAIL_FOOD" 
IS
type CA_DETAIL_FOOD_REC
IS
  record
  (
    PUB_DATE CA_DETAIL_FOOD.PUB_DATE%type ,
    RECORD_ID CA_DETAIL_FOOD.RECORD_ID%type ,
    FOOD_INTRODUCE CA_DETAIL_FOOD.FOOD_INTRODUCE%type ,
    DELIVERY_PHONE CA_DETAIL_FOOD.DELIVERY_PHONE%type ,
  --  MERCHANT_ID CA_DETAIL_FOOD.MERCHANT_ID%type ,
    SALE_PRICE CA_DETAIL_FOOD.SALE_PRICE%type );
type CA_DETAIL_FOOD_TAB
IS
  TABLE OF CA_DETAIL_FOOD_REC;
  -- insert
  PROCEDURE ins(
      p_PUB_DATE       IN CA_DETAIL_FOOD.PUB_DATE%type DEFAULT NULL ,
      p_RECORD_ID      IN CA_DETAIL_FOOD.RECORD_ID%type ,
      p_FOOD_INTRODUCE IN CA_DETAIL_FOOD.FOOD_INTRODUCE%type DEFAULT NULL ,
      p_DELIVERY_PHONE IN CA_DETAIL_FOOD.DELIVERY_PHONE%type DEFAULT NULL ,
     -- p_MERCHANT_ID    IN CA_DETAIL_FOOD.MERCHANT_ID%type DEFAULT NULL ,
      p_SALE_PRICE     IN CA_DETAIL_FOOD.SALE_PRICE%type DEFAULT NULL,
      op_ins_code out number
      );
  -- update
  PROCEDURE upd(
      p_PUB_DATE       IN CA_DETAIL_FOOD.PUB_DATE%type DEFAULT NULL ,
      p_RECORD_ID      IN CA_DETAIL_FOOD.RECORD_ID%type ,
      p_FOOD_INTRODUCE IN CA_DETAIL_FOOD.FOOD_INTRODUCE%type DEFAULT NULL ,
      p_DELIVERY_PHONE IN CA_DETAIL_FOOD.DELIVERY_PHONE%type DEFAULT NULL ,
   --   p_MERCHANT_ID    IN CA_DETAIL_FOOD.MERCHANT_ID%type DEFAULT NULL ,
      p_SALE_PRICE     IN CA_DETAIL_FOOD.SALE_PRICE%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN CA_DETAIL_FOOD.RECORD_ID%type );
END CA_PKG_DETAIL_FOOD;

/
--------------------------------------------------------
--  DDL for Package CA_PKG_FEEDBACK
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_PKG_FEEDBACK" 
IS
type COMMON_FEEDBACK_REC
IS
  record
  (
    MOBILE_TYPE COMMON_FEEDBACK.MOBILE_TYPE%type ,
    MOBILE_SOFTWARE_VERSION COMMON_FEEDBACK.MOBILE_SOFTWARE_VERSION%type ,
    USER_ID COMMON_FEEDBACK.USER_ID%type ,
    RECORD_ID COMMON_FEEDBACK.RECORD_ID%type ,
    FB_CONTENT COMMON_FEEDBACK.FB_CONTENT%type ,
    MOBILE_OS_MODEL COMMON_FEEDBACK.MOBILE_OS_MODEL%type ,
    FB_DATE COMMON_FEEDBACK.FB_DATE%type ,
    MOBILE_OS_VERSION COMMON_FEEDBACK.MOBILE_OS_VERSION%type );
type COMMON_FEEDBACK_REC_TAB
IS
  TABLE OF COMMON_FEEDBACK_REC;
  -- insert
  PROCEDURE ins(
      p_MOBILE_TYPE             IN COMMON_FEEDBACK.MOBILE_TYPE%type DEFAULT NULL ,
      p_MOBILE_SOFTWARE_VERSION IN COMMON_FEEDBACK.MOBILE_SOFTWARE_VERSION%type DEFAULT NULL ,
      p_USER_ID                 IN COMMON_FEEDBACK.USER_ID%type DEFAULT NULL ,
  --    p_RECORD_ID               IN COMMON_FEEDBACK.RECORD_ID%type ,
      p_FB_CONTENT              IN COMMON_FEEDBACK.FB_CONTENT%type DEFAULT NULL ,
      p_MOBILE_OS_MODEL         IN COMMON_FEEDBACK.MOBILE_OS_MODEL%type DEFAULT NULL ,
      p_FB_DATE                 IN COMMON_FEEDBACK.FB_DATE%type DEFAULT NULL ,
      p_MOBILE_OS_VERSION       IN COMMON_FEEDBACK.MOBILE_OS_VERSION%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_MOBILE_TYPE             IN COMMON_FEEDBACK.MOBILE_TYPE%type DEFAULT NULL ,
      p_MOBILE_SOFTWARE_VERSION IN COMMON_FEEDBACK.MOBILE_SOFTWARE_VERSION%type DEFAULT NULL ,
      p_USER_ID                 IN COMMON_FEEDBACK.USER_ID%type DEFAULT NULL ,
      p_RECORD_ID               IN COMMON_FEEDBACK.RECORD_ID%type ,
      p_FB_CONTENT              IN COMMON_FEEDBACK.FB_CONTENT%type DEFAULT NULL ,
      p_MOBILE_OS_MODEL         IN COMMON_FEEDBACK.MOBILE_OS_MODEL%type DEFAULT NULL ,
      p_FB_DATE                 IN COMMON_FEEDBACK.FB_DATE%type DEFAULT NULL ,
      p_MOBILE_OS_VERSION       IN COMMON_FEEDBACK.MOBILE_OS_VERSION%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
     IP_RECORD_ID IN COMMON_FEEDBACK.RECORD_ID%type,
     OP_DEL_CODE OUT NUMBER    
      );
      
  /*======================================查询=============================================*/   
  PROCEDURE  SEL(
    IP_RECORD_ID IN COMMON_FEEDBACK.RECORD_ID%type,
    OP_OPERATE_CODE OUT NUMBER,
    OP_SEL_RESULT OUT SYS_REFCURSOR
  );
END CA_PKG_FEEDBACK;

/
--------------------------------------------------------
--  DDL for Package CA_PKG_FOOD_COMMENT
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_PKG_FOOD_COMMENT" 
IS
type CA_FOOD_COMMENT_REC
IS
  record
  (
    MARK_VALUE CA_FOOD_COMMENT.MARK_VALUE%type ,
    COMMENT_DATE CA_FOOD_COMMENT.COMMENT_DATE%type ,
    USER_ID CA_FOOD_COMMENT.USER_ID%type ,
    RECORD_ID CA_FOOD_COMMENT.RECORD_ID%type ,
    COMMENT_CONTENT CA_FOOD_COMMENT.COMMENT_CONTENT%type ,
    COMMENT_TYPE CA_FOOD_COMMENT.COMMENT_TYPE%type ,
    REF_ID CA_FOOD_COMMENT.REF_ID%type ,
    PARENT_ID CA_FOOD_COMMENT.PARENT_ID%type );
type CA_FOOD_COMMENT_TAB
IS
  TABLE OF CA_FOOD_COMMENT_REC;
  -- insert
  PROCEDURE ins(
      p_MARK_VALUE      IN CA_FOOD_COMMENT.MARK_VALUE%type DEFAULT NULL ,
      p_COMMENT_DATE    IN CA_FOOD_COMMENT.COMMENT_DATE%type DEFAULT NULL ,
      p_USER_ID         IN CA_FOOD_COMMENT.USER_ID%type DEFAULT NULL ,
    --  p_RECORD_ID       IN CA_FOOD_COMMENT.RECORD_ID%type ,
      p_COMMENT_CONTENT IN CA_FOOD_COMMENT.COMMENT_CONTENT%type DEFAULT NULL ,
      p_COMMENT_TYPE    IN CA_FOOD_COMMENT.COMMENT_TYPE%type DEFAULT NULL ,
      p_REF_ID          IN CA_FOOD_COMMENT.REF_ID%type DEFAULT NULL ,
      p_PARENT_ID       IN CA_FOOD_COMMENT.PARENT_ID%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_MARK_VALUE      IN CA_FOOD_COMMENT.MARK_VALUE%type DEFAULT NULL ,
      p_COMMENT_DATE    IN CA_FOOD_COMMENT.COMMENT_DATE%type DEFAULT NULL ,
      p_USER_ID         IN CA_FOOD_COMMENT.USER_ID%type DEFAULT NULL ,
      p_RECORD_ID       IN CA_FOOD_COMMENT.RECORD_ID%type ,
      p_COMMENT_CONTENT IN CA_FOOD_COMMENT.COMMENT_CONTENT%type DEFAULT NULL ,
      p_COMMENT_TYPE    IN CA_FOOD_COMMENT.COMMENT_TYPE%type DEFAULT NULL ,
      p_REF_ID          IN CA_FOOD_COMMENT.REF_ID%type DEFAULT NULL ,
      p_PARENT_ID       IN CA_FOOD_COMMENT.PARENT_ID%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN CA_FOOD_COMMENT.RECORD_ID%type );
END CA_PKG_FOOD_COMMENT;

/
--------------------------------------------------------
--  DDL for Package CA_PKG_LOCATION
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_PKG_LOCATION" 
IS

  /*=======================================更新用户最新位置信息.并获取附近用户=====================================*/
   PROCEDURE UPD_LOACTION(
    IP_USER_ID IN NVARCHAR2,
    IP_LONGTITUDE IN NUMBER,
    IP_LATITUDE IN NUMBER,
    OP_SEL_CODE OUT NUMBER,
    OP_SEL_RESULT OUT SYS_REFCURSOR
  );
  /*===============================查询统一专业的昵称(因为可能会抛出异常,所以单独进行处理)==============*/
  FUNCTION SEL_ONEMARJOR_NAME 
  (
    IP_GRADE_NO IN NVARCHAR2,
    IP_COLLEGE_NO  IN NVARCHAR2,
    IP_MARJOR_NO IN NVARCHAR2
  )
  RETURN NVARCHAR2
  ;
  /*=================================查询上同一节课的人============================================*/
  PROCEDURE SEL_ONE_LESSON(
    IP_USER_ID IN NVARCHAR2,
    IP_WEEK IN NUMBER,
    IP_DAY_OF_WEEK IN NUMBER,
    IP_NO_OF_DAY IN NUMBER,
    IP_LESSON_NO IN NVARCHAR2,
    OP_SEL_CODE OUT NUMBER,
    OP_SEL_RESULT OUT SYS_REFCURSOR
  );
  
  
END CA_PKG_LOCATION;

/
--------------------------------------------------------
--  DDL for Package CA_PKG_MERCHANT
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_PKG_MERCHANT" 
IS
type CA_MERCHANT_REC
IS
  record
  (
    ODER_LIST CA_MERCHANT.ODER_LIST%type ,
    LAST_MODIFY CA_MERCHANT.LAST_MODIFY%type ,
    LOGIN_ID CA_MERCHANT.LOGIN_ID%type ,
    REG_IP CA_MERCHANT.REG_IP%type ,
    REG_DATE CA_MERCHANT.REG_DATE%type ,
    CONTACT_NAME CA_MERCHANT.CONTACT_NAME%type ,
    RECORD_ID CA_MERCHANT.RECORD_ID%type ,
    CONTACT_PHONE CA_MERCHANT.CONTACT_PHONE%type ,
    MERCHANT_NAME CA_MERCHANT.MERCHANT_NAME%type ,
    NEW_ORDER CA_MERCHANT.NEW_ORDER%type ,
    ENCRYPTPASSWORD CA_MERCHANT.ENCRYPTPASSWORD%type ,
    MERCHANT_ADDRESS CA_MERCHANT.MERCHANT_ADDRESS%type );
type CA_MERCHANT_TAB
IS
  TABLE OF CA_MERCHANT_REC;
  -- insert
  PROCEDURE ins(
      p_LOGIN_ID         IN CA_MERCHANT.LOGIN_ID%type DEFAULT NULL ,
      p_REG_IP           IN CA_MERCHANT.REG_IP%type DEFAULT NULL ,
      p_CONTACT_NAME     IN CA_MERCHANT.CONTACT_NAME%type DEFAULT NULL ,
      p_CONTACT_PHONE    IN CA_MERCHANT.CONTACT_PHONE%type DEFAULT NULL ,
      p_MERCHANT_NAME    IN CA_MERCHANT.MERCHANT_NAME%type DEFAULT NULL ,
      p_ENCRYPTPASSWORD  IN CA_MERCHANT.ENCRYPTPASSWORD%type DEFAULT NULL ,
      p_MERCHANT_ADDRESS IN CA_MERCHANT.MERCHANT_ADDRESS%type DEFAULT NULL,
      OP_INS_CODE OUT NUMBER
      
      );
  -- update
  PROCEDURE upd(
      p_ODER_LIST        IN CA_MERCHANT.ODER_LIST%type DEFAULT NULL ,
      p_LAST_MODIFY      IN CA_MERCHANT.LAST_MODIFY%type DEFAULT NULL ,
      p_LOGIN_ID         IN CA_MERCHANT.LOGIN_ID%type DEFAULT NULL ,
      p_REG_IP           IN CA_MERCHANT.REG_IP%type DEFAULT NULL ,
      p_REG_DATE         IN CA_MERCHANT.REG_DATE%type DEFAULT NULL ,
      p_CONTACT_NAME     IN CA_MERCHANT.CONTACT_NAME%type DEFAULT NULL ,
      p_RECORD_ID        IN CA_MERCHANT.RECORD_ID%type ,
      p_CONTACT_PHONE    IN CA_MERCHANT.CONTACT_PHONE%type DEFAULT NULL ,
      p_MERCHANT_NAME    IN CA_MERCHANT.MERCHANT_NAME%type DEFAULT NULL ,
      p_NEW_ORDER        IN CA_MERCHANT.NEW_ORDER%type DEFAULT NULL ,
      p_ENCRYPTPASSWORD  IN CA_MERCHANT.ENCRYPTPASSWORD%type DEFAULT NULL ,
      p_MERCHANT_ADDRESS IN CA_MERCHANT.MERCHANT_ADDRESS%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN CA_MERCHANT.RECORD_ID%type );
  /*==================================检测用户名是否存在=================================================*/
  PROCEDURE CHECK_USER_EXIST(
    IP_LOGIN_ID IN NVARCHAR2,
    OP_CHK_CODE OUT NUMBER
     
  );
END CA_PKG_MERCHANT;

/
--------------------------------------------------------
--  DDL for Package CA_PKG_RUNTIME_ANAL
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_PKG_RUNTIME_ANAL" 
IS
type CA_RUNTIME_ANAL_REC
IS
  record
  (
    OBJECT_NAME CA_RUNTIME_ANAL.OBJECT_NAME%type ,
    RECORD_ID CA_RUNTIME_ANAL.RECORD_ID%type ,
    COUNT_TIME CA_RUNTIME_ANAL.COUNT_TIME%type ,
    OBJECT_TYPE CA_RUNTIME_ANAL.OBJECT_TYPE%type ,
    AVER_TIME CA_RUNTIME_ANAL.AVER_TIME%type ,
    RECORD_DATE CA_RUNTIME_ANAL.RECORD_DATE%type ,
    EXE_TIMES CA_RUNTIME_ANAL.EXE_TIMES%type );
type CA_RUNTIME_ANAL_TAB
IS
  TABLE OF CA_RUNTIME_ANAL_REC;
  -- insert
  PROCEDURE ins(
      p_OBJECT_NAME IN CA_RUNTIME_ANAL.OBJECT_NAME%type DEFAULT NULL ,
     -- p_RECORD_ID   IN CA_RUNTIME_ANAL.RECORD_ID%type ,
      p_COUNT_TIME  IN CA_RUNTIME_ANAL.COUNT_TIME%type ,
      p_OBJECT_TYPE IN CA_RUNTIME_ANAL.OBJECT_TYPE%type ,
      p_AVER_TIME   IN CA_RUNTIME_ANAL.AVER_TIME%type DEFAULT NULL ,
      p_RECORD_DATE IN CA_RUNTIME_ANAL.RECORD_DATE%type DEFAULT NULL ,
      p_EXE_TIMES   IN CA_RUNTIME_ANAL.EXE_TIMES%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_OBJECT_NAME IN CA_RUNTIME_ANAL.OBJECT_NAME%type DEFAULT NULL ,
      p_RECORD_ID   IN CA_RUNTIME_ANAL.RECORD_ID%type ,
      p_COUNT_TIME  IN CA_RUNTIME_ANAL.COUNT_TIME%type ,
      p_OBJECT_TYPE IN CA_RUNTIME_ANAL.OBJECT_TYPE%type ,
      p_AVER_TIME   IN CA_RUNTIME_ANAL.AVER_TIME%type DEFAULT NULL ,
      p_RECORD_DATE IN CA_RUNTIME_ANAL.RECORD_DATE%type DEFAULT NULL ,
      p_EXE_TIMES   IN CA_RUNTIME_ANAL.EXE_TIMES%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN CA_RUNTIME_ANAL.RECORD_ID%type );
END CA_PKG_RUNTIME_ANAL;

/
--------------------------------------------------------
--  DDL for Package CA_PKG_SENSITIVE
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_PKG_SENSITIVE" 
IS
type CA_PKG_SENSITIVE_REC
IS
  record
  (
    SOURCE CA_SENSITIVE.SOURCE%type ,
    RECORD_ID CA_SENSITIVE.RECORD_ID%type ,
    SENSITIVE_STR CA_SENSITIVE.SENSITIVE_STR%type );
type CA_PKG_SENSITIVE_TAB
IS
  TABLE OF CA_PKG_SENSITIVE_REC;
  -- insert
  PROCEDURE ins(
      p_SOURCE        IN CA_SENSITIVE.SOURCE%type ,
     -- p_RECORD_ID     IN CA_SENSITIVE.RECORD_ID%type ,
      p_SENSITIVE_STR IN CA_SENSITIVE.SENSITIVE_STR%type DEFAULT NULL,
      OP_OPERATE_CODE OUT NUMBER
      );
  -- update
  PROCEDURE upd(
      p_SOURCE        IN CA_SENSITIVE.SOURCE%type ,
      p_RECORD_ID     IN CA_SENSITIVE.RECORD_ID%type ,
      p_SENSITIVE_STR IN CA_SENSITIVE.SENSITIVE_STR%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN CA_SENSITIVE.RECORD_ID%type,
      OP_OPERATE_CODE OUT NUMBER
      );
      
  /*=============================分页查询======================================*/
  PROCEDURE PAGE_SENSITIVE(
    IP_PAGE_INDEX IN NUMBER,--索引
    IP_PAGE_SIZE  IN NUMBER,
    OP_SELECT_RESULT OUT SYS_REFCURSOR,
    OP_SELECT_CODE OUT NUMBER,
    OP_SUMALL OUT NUMBER );
    
END CA_PKG_SENSITIVE;

/
--------------------------------------------------------
--  DDL for Package CA_PKG_SIMPLE_FOOD
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_PKG_SIMPLE_FOOD" 
IS
type CA_SIMPLE_FOOD_REC
IS
  record
  (
    FOOD_CATEGORY_ID CA_SIMPLE_FOOD.FOOD_CATEGORY_ID%type ,
    WORSE_MARK CA_SIMPLE_FOOD.WORSE_MARK%type ,
    MARK_VALUE CA_SIMPLE_FOOD.MARK_VALUE%type ,
    RECORD_ID CA_SIMPLE_FOOD.RECORD_ID%type ,
    FOOD_TYPE_ID CA_SIMPLE_FOOD.FOOD_TYPE_ID%type ,
    FOOD_IMG CA_SIMPLE_FOOD.FOOD_IMG%type ,
    FOOD_PRICE CA_SIMPLE_FOOD.FOOD_PRICE%type ,
    GOOD_MARK CA_SIMPLE_FOOD.GOOD_MARK%type ,
    FOOD_NAME CA_SIMPLE_FOOD.FOOD_NAME%type );
type CA_SIMPLE_FOOD_TAB
IS
  TABLE OF CA_SIMPLE_FOOD_REC;
  -- insert
  PROCEDURE ins(
     p_FOOD_CATEGORY_ID IN CA_SIMPLE_FOOD.FOOD_CATEGORY_ID%type DEFAULT NULL ,
      p_WORSE_MARK       IN CA_SIMPLE_FOOD.WORSE_MARK%type DEFAULT NULL ,
      p_MARK_VALUE       IN CA_SIMPLE_FOOD.MARK_VALUE%type DEFAULT NULL ,
      --p_RECORD_ID        IN CA_SIMPLE_FOOD.RECORD_ID%type ,
      p_FOOD_TYPE_ID     IN CA_SIMPLE_FOOD.FOOD_TYPE_ID%type DEFAULT NULL ,
      p_FOOD_IMG         IN CA_SIMPLE_FOOD.FOOD_IMG%type DEFAULT NULL ,
      p_FOOD_PRICE       IN CA_SIMPLE_FOOD.FOOD_PRICE%type DEFAULT NULL ,
      p_GOOD_MARK        IN CA_SIMPLE_FOOD.GOOD_MARK%type DEFAULT NULL ,
      p_FOOD_NAME        IN CA_SIMPLE_FOOD.FOOD_NAME%type DEFAULT NULL,
      --详情
     -- p_PUB_DATE       IN CA_DETAIL_FOOD.PUB_DATE%type DEFAULT NULL ,
      --p_RECORD_ID      IN CA_DETAIL_FOOD.RECORD_ID%type ,
      p_FOOD_INTRODUCE IN CA_DETAIL_FOOD.FOOD_INTRODUCE%type DEFAULT NULL ,
      p_DELIVERY_PHONE IN CA_DETAIL_FOOD.DELIVERY_PHONE%type DEFAULT NULL ,
      p_MERCHANT_ID    IN CA_SIMPLE_FOOD.MERCHANT_ID%type DEFAULT NULL ,
      p_SALE_PRICE     IN CA_DETAIL_FOOD.SALE_PRICE%type DEFAULT NULL,
      OP_INS_CODE out number
      );
  -- update
  PROCEDURE upd(
      p_FOOD_CATEGORY_ID IN CA_SIMPLE_FOOD.FOOD_CATEGORY_ID%type DEFAULT NULL ,
      p_WORSE_MARK       IN CA_SIMPLE_FOOD.WORSE_MARK%type DEFAULT NULL ,
      p_MARK_VALUE       IN CA_SIMPLE_FOOD.MARK_VALUE%type DEFAULT NULL ,
      p_RECORD_ID        IN CA_SIMPLE_FOOD.RECORD_ID%type ,
      p_FOOD_TYPE_ID     IN CA_SIMPLE_FOOD.FOOD_TYPE_ID%type DEFAULT NULL ,
      p_FOOD_IMG         IN CA_SIMPLE_FOOD.FOOD_IMG%type DEFAULT NULL ,
      p_FOOD_PRICE       IN CA_SIMPLE_FOOD.FOOD_PRICE%type DEFAULT NULL ,
      p_GOOD_MARK        IN CA_SIMPLE_FOOD.GOOD_MARK%type DEFAULT NULL ,
      p_FOOD_NAME        IN CA_SIMPLE_FOOD.FOOD_NAME%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN CA_SIMPLE_FOOD.RECORD_ID%type );
       /*=======================================获取指定的类别内容数据------------------------------------------------------*/
 PROCEDURE SEL_BY_TYPE(
  IP_TYPE_ID IN NUMBER,
  OP_SEL_CODE OUT NUMBER,
  OP_SEL_RESULT OUT SYS_REFCURSOR
 );
 
 /*===================================数据查询===============================================*/
 procedure sel_by_id
 (
  IP_RECORD_ID IN NUMBER,
  OP_SEL_CODE OUT NUMBER,
  OP_SEL_RESULT OUT SYS_REFCURSOR
 );
 
 
/*=========================================获取推荐===========================================*/
PROCEDURE SEL_RECOMMAND
(
  
  IP_NEED_SIZE IN NUMBER,
  IP_KEYWORDS IN NVARCHAR2,
  OP_SEL_CODE OUT NUMBER,
  OP_SEL_RESULT OUT SYS_REFCURSOR
);


/*==========================================评分=============================================*/
 PROCEDURE DO_MARK(
  IP_MARK_TYPE IN NUMBER,
  IP_RECORD_ID IN NUMBER,
  OP_LAST_MARK_VALUE OUT VARCHAR2,
  OP_OP_CODE OUT NUMBER
);

PROCEDURE SEL_BY_ID_V2
 (
  IP_RECORD_ID IN NUMBER,
    IP_USER_ID IN  COMMON_USER.LOGIN_ID%TYPE,
  OP_SEL_CODE OUT NUMBER,
  OP_SEL_RESULT OUT SYS_REFCURSOR,
--  OP_CODE OUT NUMBER,
  OP_MSG  OUT NVARCHAR2	
 );

PROCEDURE SEL_BY_TYPE_V2(
  IP_TYPE_ID IN NUMBER,
  OP_SEL_CODE OUT NUMBER,
  OP_SEL_RESULT OUT SYS_REFCURSOR,
  OP_ROWCOUNT OUT NUMBER,
  OP_MSG OUT NVARCHAR2  
 );
 
  PROCEDURE SEL_RECOMMAND_V2
(
  
  IP_NEED_SIZE IN NUMBER,
  IP_KEYWORDS IN NVARCHAR2,
  OP_SEL_CODE OUT NUMBER,
  OP_SEL_RESULT OUT SYS_REFCURSOR,
  OP_ROWCOUNT OUT NUMBER,
  OP_MSG OUT NVARCHAR2
);
END CA_PKG_SIMPLE_FOOD;

/
--------------------------------------------------------
--  DDL for Package CA_RUNTIME_ANAL_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_RUNTIME_ANAL_TAPI" 
IS
type CA_RUNTIME_ANAL_tapi_rec
IS
  record
  (
    OBJECT_NAME CA_RUNTIME_ANAL.OBJECT_NAME%type ,
    RECORD_ID CA_RUNTIME_ANAL.RECORD_ID%type ,
    COUNT_TIME CA_RUNTIME_ANAL.COUNT_TIME%type ,
    OBJECT_TYPE CA_RUNTIME_ANAL.OBJECT_TYPE%type ,
    AVER_TIME CA_RUNTIME_ANAL.AVER_TIME%type ,
    EXE_TIMES CA_RUNTIME_ANAL.EXE_TIMES%type );
type CA_RUNTIME_ANAL_tapi_tab
IS
  TABLE OF CA_RUNTIME_ANAL_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_OBJECT_NAME IN CA_RUNTIME_ANAL.OBJECT_NAME%type DEFAULT NULL ,
      p_RECORD_ID   IN CA_RUNTIME_ANAL.RECORD_ID%type ,
      p_COUNT_TIME  IN CA_RUNTIME_ANAL.COUNT_TIME%type ,
      p_OBJECT_TYPE IN CA_RUNTIME_ANAL.OBJECT_TYPE%type ,
      p_AVER_TIME   IN CA_RUNTIME_ANAL.AVER_TIME%type DEFAULT NULL ,
      p_EXE_TIMES   IN CA_RUNTIME_ANAL.EXE_TIMES%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_OBJECT_NAME IN CA_RUNTIME_ANAL.OBJECT_NAME%type DEFAULT NULL ,
      p_RECORD_ID   IN CA_RUNTIME_ANAL.RECORD_ID%type ,
      p_COUNT_TIME  IN CA_RUNTIME_ANAL.COUNT_TIME%type ,
      p_OBJECT_TYPE IN CA_RUNTIME_ANAL.OBJECT_TYPE%type ,
      p_AVER_TIME   IN CA_RUNTIME_ANAL.AVER_TIME%type DEFAULT NULL ,
      p_EXE_TIMES   IN CA_RUNTIME_ANAL.EXE_TIMES%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
    );
END CA_RUNTIME_ANAL_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_SIMPLE_FOOD_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_SIMPLE_FOOD_TAPI" 
IS
type CA_SIMPLE_FOOD_tapi_rec
IS
  record
  (
    FOOD_CATEGORY_ID CA_SIMPLE_FOOD.FOOD_CATEGORY_ID%type ,
    WORSE_MARK CA_SIMPLE_FOOD.WORSE_MARK%type ,
    MARK_VALUE CA_SIMPLE_FOOD.MARK_VALUE%type ,
    RECORD_ID CA_SIMPLE_FOOD.RECORD_ID%type ,
    FOOD_TYPE_ID CA_SIMPLE_FOOD.FOOD_TYPE_ID%type ,
    FOOD_IMG CA_SIMPLE_FOOD.FOOD_IMG%type ,
    FOOD_PRICE CA_SIMPLE_FOOD.FOOD_PRICE%type ,
    MERCHANT_ID CA_SIMPLE_FOOD.MERCHANT_ID%type ,
    GOOD_MARK CA_SIMPLE_FOOD.GOOD_MARK%type ,
    FOOD_NAME CA_SIMPLE_FOOD.FOOD_NAME%type );
type CA_SIMPLE_FOOD_tapi_tab
IS
  TABLE OF CA_SIMPLE_FOOD_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_FOOD_CATEGORY_ID IN CA_SIMPLE_FOOD.FOOD_CATEGORY_ID%type DEFAULT NULL ,
      p_WORSE_MARK       IN CA_SIMPLE_FOOD.WORSE_MARK%type DEFAULT NULL ,
      p_MARK_VALUE       IN CA_SIMPLE_FOOD.MARK_VALUE%type DEFAULT NULL ,
      p_RECORD_ID        IN CA_SIMPLE_FOOD.RECORD_ID%type ,
      p_FOOD_TYPE_ID     IN CA_SIMPLE_FOOD.FOOD_TYPE_ID%type DEFAULT NULL ,
      p_FOOD_IMG         IN CA_SIMPLE_FOOD.FOOD_IMG%type DEFAULT NULL ,
      p_FOOD_PRICE       IN CA_SIMPLE_FOOD.FOOD_PRICE%type DEFAULT NULL ,
      p_MERCHANT_ID      IN CA_SIMPLE_FOOD.MERCHANT_ID%type ,
      p_GOOD_MARK        IN CA_SIMPLE_FOOD.GOOD_MARK%type DEFAULT NULL ,
      p_FOOD_NAME        IN CA_SIMPLE_FOOD.FOOD_NAME%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_FOOD_CATEGORY_ID IN CA_SIMPLE_FOOD.FOOD_CATEGORY_ID%type DEFAULT NULL ,
      p_WORSE_MARK       IN CA_SIMPLE_FOOD.WORSE_MARK%type DEFAULT NULL ,
      p_MARK_VALUE       IN CA_SIMPLE_FOOD.MARK_VALUE%type DEFAULT NULL ,
      p_RECORD_ID        IN CA_SIMPLE_FOOD.RECORD_ID%type ,
      p_FOOD_TYPE_ID     IN CA_SIMPLE_FOOD.FOOD_TYPE_ID%type DEFAULT NULL ,
      p_FOOD_IMG         IN CA_SIMPLE_FOOD.FOOD_IMG%type DEFAULT NULL ,
      p_FOOD_PRICE       IN CA_SIMPLE_FOOD.FOOD_PRICE%type DEFAULT NULL ,
      p_MERCHANT_ID      IN CA_SIMPLE_FOOD.MERCHANT_ID%type ,
      p_GOOD_MARK        IN CA_SIMPLE_FOOD.GOOD_MARK%type DEFAULT NULL ,
      p_FOOD_NAME        IN CA_SIMPLE_FOOD.FOOD_NAME%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN CA_SIMPLE_FOOD.RECORD_ID%type );
      PROCEDURE CHECK_NAME(
	IP_FOOD_NAME IN CA_SIMPLE_FOOD.FOOD_NAME%TYPE,
	IP_USER_ID IN CA_MERCHANT.LOGIN_ID%TYPE,
	OP_CHECK_CODE OUT NUMBER
);

PROCEDURE PAGE_FOOD(
	IP_PAGE_INDEX IN NUMBER,--索引
    IP_PAGE_SIZE  IN NUMBER,--页面大小
	IP_QUERY_STATE IN NUMBER,--是否启用查询
	IP_TYPE_ID		IN CA_SIMPLE_FOOD.FOOD_TYPE_ID%TYPE,--类型
	IP_CATEGORY_ID IN CA_SIMPLE_FOOD.FOOD_CATEGORY_ID%TYPE,--菜系
	IP_MERCHANT_ID IN CA_SIMPLE_FOOD.MERCHANT_ID%TYPE,--商家ID
    IP_KEYWORDS IN NVARCHAR2,--关键字
    SELECT_RESULT OUT SYS_REFCURSOR,--查询结果
	OP_ROWCOUNT OUT NUMBER  ,--数据量
	OP_CODE OUT NUMBER  --操作代码
);

PROCEDURE UPDATE_PRICE(
	IP_FOOD_ID IN NUMBER,--食品ID
	IP_FOOD_PRICE IN NUMBER,--新的价格
	OP_MSG  OUT NVARCHAR2--更新消息
);

PROCEDURE SEL_FOOD(
	  IP_MERCHANT_ID 		IN 			NVARCHAR2,
	  IP_REC_ID 			IN 			NUMBER,
    SELECT_RESULT 		OUT 		SYS_REFCURSOR,--查询结果
    OP_CODE 			OUT 		NUMBER,---操作代码
    OP_MSG 				OUT 		NVARCHAR2
);

PROCEDURE UPDATE_FOOD(
	IP_MERCHANT_ID 		IN 			NVARCHAR2,
	IP_REC_ID 			IN 			NUMBER,
	IP_TYPE_ID			IN 			NUMBER,
	IP_CATEGORY_ID		IN 			NUMBER,
	IP_FOOD_PRICE		IN 			NUMBER,
	IP_FOOD_DESC		IN 			NVARCHAR2,
    OP_CODE 			OUT 		NUMBER,---操作代码
    OP_MSG 				OUT 		NVARCHAR2
);

PROCEDURE DELETE_FOOD(
	IP_MERCHANT_ID 		IN 			NVARCHAR2,
	IP_REC_ID 			IN 			NUMBER,
	OP_CODE 			OUT 		NUMBER,---操作代码
    OP_MSG 				OUT 		NVARCHAR2
);
END CA_SIMPLE_FOOD_tapi;

/
--------------------------------------------------------
--  DDL for Package CA_SITE_COUNT_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CA_SITE_COUNT_TAPI" 
IS
type CA_SITE_COUNT_tapi_rec
IS
  record
  (
    SITE_NAME CA_SITE_COUNT.SITE_NAME%type ,
    TOTAL CA_SITE_COUNT.TOTAL%type );
type CA_SITE_COUNT_tapi_tab
IS
  TABLE OF CA_SITE_COUNT_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_SITE_NAME IN CA_SITE_COUNT.SITE_NAME%type ,
      p_TOTAL     IN CA_SITE_COUNT.TOTAL%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_SITE_NAME IN CA_SITE_COUNT.SITE_NAME%type ,
      p_TOTAL     IN CA_SITE_COUNT.TOTAL%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_SITE_NAME IN CA_SITE_COUNT.SITE_NAME%type );
  PROCEDURE SEL_COUNT
  (
    IP_SITE_NAME IN CA_SITE_COUNT.SITE_NAME%type,
    OP_TOTAL OUT CA_SITE_COUNT.TOTAL%type 
  );
END CA_SITE_COUNT_tapi;

/
--------------------------------------------------------
--  DDL for Package CDUTAPP_PACKAGE
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CDUTAPP_PACKAGE" 
as
  type test_cursor is ref cursor;
end cdutapp_package;

/
--------------------------------------------------------
--  DDL for Package CHAT_PKG_USER
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."CHAT_PKG_USER" 
IS

  /*===================--LOAD USER============================================================*/
  
  PROCEDURE LOAD_USER(
    IP_LOGIN_ID IN NVARCHAR2,
    OP_SELECT_RESULT OUT SYS_REFCURSOR,
    OP_OPERATE_CODE OUT NUMBER
  ); 
  
END CHAT_PKG_USER;

/
--------------------------------------------------------
--  DDL for Package COMMON_ACTIVITY_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."COMMON_ACTIVITY_TAPI" 
IS
type COMMON_ACTIVITY_tapi_rec
IS
  record
  (
    PUB_CONTENT COMMON_ACTIVITY.PUB_CONTENT%type ,
    PUB_IP COMMON_ACTIVITY.PUB_IP%type ,
    PUB_DATE COMMON_ACTIVITY.PUB_DATE%type ,
    PUB_TITLE COMMON_ACTIVITY.PUB_TITLE%type ,
    RECORD_ID COMMON_ACTIVITY.RECORD_ID%type ,
    U_LOGINID COMMON_ACTIVITY.U_LOGINID%type );
type COMMON_ACTIVITY_tapi_tab
IS
  TABLE OF COMMON_ACTIVITY_tapi_rec;
  -- insert
  PROCEDURE ins(
   p_PUB_CONTENT IN COMMON_ACTIVITY.PUB_CONTENT%type DEFAULT NULL ,
    p_PUB_IP      IN COMMON_ACTIVITY.PUB_IP%type DEFAULT NULL ,
  p_PUB_TITLE   IN COMMON_ACTIVITY.PUB_TITLE%type DEFAULT NULL ,
  p_notify_user in number,
    p_U_LOGINID   IN COMMON_ACTIVITY.U_LOGINID%type DEFAULT NULL,
    op_op_code out number);
  -- update
  PROCEDURE upd(
      p_PUB_CONTENT IN COMMON_ACTIVITY.PUB_CONTENT%type DEFAULT NULL ,
      p_PUB_IP      IN COMMON_ACTIVITY.PUB_IP%type DEFAULT NULL ,
      p_PUB_DATE    IN COMMON_ACTIVITY.PUB_DATE%type DEFAULT NULL ,
      p_PUB_TITLE   IN COMMON_ACTIVITY.PUB_TITLE%type DEFAULT NULL ,
      p_RECORD_ID   IN COMMON_ACTIVITY.RECORD_ID%type ,
      p_U_LOGINID   IN COMMON_ACTIVITY.U_LOGINID%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN COMMON_ACTIVITY.RECORD_ID%type );
END COMMON_ACTIVITY_tapi;

/
--------------------------------------------------------
--  DDL for Package COMMON_MESSAGE_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."COMMON_MESSAGE_TAPI" 
IS
type COMMON_MESSAGE_tapi_rec
IS
  record
  (
    PUB_IP COMMON_MESSAGE.PUB_IP%type ,
    PUB_DATE COMMON_MESSAGE.PUB_DATE%type ,
    RECORD_ID COMMON_MESSAGE.RECORD_ID%type ,
    MSG_CONTENT COMMON_MESSAGE.MSG_CONTENT%type ,
    MSG_TITLE COMMON_MESSAGE.MSG_TITLE%type ,
    PUB_AUTHOR COMMON_MESSAGE.PUB_AUTHOR%type ,
    MSG_TYPE COMMON_MESSAGE.MSG_TYPE%type ,
    MSG_METHOD COMMON_MESSAGE.MSG_METHOD%type ,
    PUSH_TYPE COMMON_MESSAGE.PUSH_TYPE%type );
type COMMON_MESSAGE_tapi_tab
IS
  TABLE OF COMMON_MESSAGE_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_PUB_IP      IN COMMON_MESSAGE.PUB_IP%type DEFAULT NULL ,
      p_PUB_DATE    IN COMMON_MESSAGE.PUB_DATE%type DEFAULT NULL ,
      p_RECORD_ID   IN COMMON_MESSAGE.RECORD_ID%type ,
      p_MSG_CONTENT IN COMMON_MESSAGE.MSG_CONTENT%type DEFAULT NULL ,
      p_MSG_TITLE   IN COMMON_MESSAGE.MSG_TITLE%type DEFAULT NULL ,
      p_PUB_AUTHOR  IN COMMON_MESSAGE.PUB_AUTHOR%type DEFAULT NULL ,
      p_MSG_TYPE    IN COMMON_MESSAGE.MSG_TYPE%type DEFAULT NULL ,
      p_MSG_METHOD  IN COMMON_MESSAGE.MSG_METHOD%type DEFAULT NULL ,
      p_PUSH_TYPE   IN COMMON_MESSAGE.PUSH_TYPE%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_PUB_IP      IN COMMON_MESSAGE.PUB_IP%type DEFAULT NULL ,
      p_PUB_DATE    IN COMMON_MESSAGE.PUB_DATE%type DEFAULT NULL ,
      p_RECORD_ID   IN COMMON_MESSAGE.RECORD_ID%type ,
      p_MSG_CONTENT IN COMMON_MESSAGE.MSG_CONTENT%type DEFAULT NULL ,
      p_MSG_TITLE   IN COMMON_MESSAGE.MSG_TITLE%type DEFAULT NULL ,
      p_PUB_AUTHOR  IN COMMON_MESSAGE.PUB_AUTHOR%type DEFAULT NULL ,
      p_MSG_TYPE    IN COMMON_MESSAGE.MSG_TYPE%type DEFAULT NULL ,
      p_MSG_METHOD  IN COMMON_MESSAGE.MSG_METHOD%type DEFAULT NULL ,
      p_PUSH_TYPE   IN COMMON_MESSAGE.PUSH_TYPE%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN COMMON_MESSAGE.RECORD_ID%type );
END COMMON_MESSAGE_tapi;

/
--------------------------------------------------------
--  DDL for Package COMMON_USER_FAV_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."COMMON_USER_FAV_TAPI" 
IS
type COMMON_USER_FAV_tapi_rec
IS
  record
  (
    LOGIN_ID COMMON_USER_FAV.LOGIN_ID%type ,
    DATELINE COMMON_USER_FAV.DATELINE%type ,
    FAVID COMMON_USER_FAV.FAVID%type ,
    ID COMMON_USER_FAV.ID%type ,
    IDTYPE COMMON_USER_FAV.IDTYPE%type ,
    TITLE COMMON_USER_FAV.TITLE%type );
type COMMON_USER_FAV_tapi_tab
IS
  TABLE OF COMMON_USER_FAV_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_LOGIN_ID IN COMMON_USER_FAV.LOGIN_ID%type DEFAULT NULL ,
      p_ID       IN COMMON_USER_FAV.ID%type DEFAULT NULL ,
      p_IDTYPE   IN COMMON_USER_FAV.IDTYPE%type DEFAULT NULL ,
      p_TITLE    IN COMMON_USER_FAV.TITLE%type DEFAULT NULL,
      OP_OP_CODE OUT NUMBER
      );
  -- update
  PROCEDURE upd(
      p_LOGIN_ID IN COMMON_USER_FAV.LOGIN_ID%type DEFAULT NULL ,
      p_DATELINE IN COMMON_USER_FAV.DATELINE%type DEFAULT NULL ,
      p_FAVID    IN COMMON_USER_FAV.FAVID%type ,
      p_ID       IN COMMON_USER_FAV.ID%type DEFAULT NULL ,
      p_IDTYPE   IN COMMON_USER_FAV.IDTYPE%type DEFAULT NULL ,
      p_TITLE    IN COMMON_USER_FAV.TITLE%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_FAVID IN COMMON_USER_FAV.FAVID%type );
      
  --sel is fav     ?
  PROCEDURE ISFAV(
    IP_USER_ID IN NVARCHAR2,
    IP_RECORD_ID IN NUMBER,
    IP_TYPE_ID IN NUMBER,
    OP_IS_FAV OUT NUMBER,
    OP_OP_CODE OUT NUMBER
  );
  PROCEDURE ISFAV_V2(
    IP_USER_ID IN NVARCHAR2,
    IP_RECORD_ID IN NUMBER,
    IP_TYPE_ID IN NUMBER,
    OP_IS_FAV OUT NUMBER,
    OP_OP_CODE OUT NUMBER
  );
  --cancel fav
    PROCEDURE CANCEL_FAV(
        IP_USER_ID IN NVARCHAR2,
        IP_RECORD_ID IN NUMBER,
        IP_TYPE_ID IN NUMBER,
        OP_OP_C0DE OUT NUMBER
    );
    
   --获取用户收藏列表
   PROCEDURE SEL_FAV_USER(
     IP_PAGE_INDEX IN NUMBER,
    IP_PAGE_SIZE IN NUMBER,
    IP_USER_ID IN NVARCHAR2,
    OP_SELECT_RESULT OUT SYS_REFCURSOR,
    OP_OPERATE_CODE OUT NUMBER
   );
   
   FUNCTION ISFAV_V3(
    IP_USER_ID IN NVARCHAR2,
    IP_RECORD_ID IN NUMBER,
    IP_TYPE_ID IN NUMBER
  )
  RETURN   NUMBER;
  
  PROCEDURE INSV2(
      P_LOGIN_ID IN COMMON_USER_FAV.LOGIN_ID%TYPE DEFAULT NULL ,
      P_ID       IN COMMON_USER_FAV.ID%TYPE DEFAULT NULL ,
      P_IDTYPE   IN COMMON_USER_FAV.IDTYPE%TYPE DEFAULT NULL ,
      P_TITLE    IN COMMON_USER_FAV.TITLE%TYPE DEFAULT NULL,
      OP_OP_CODE OUT NUMBER,
	  OP_MSG OUT NVARCHAR2
      );
      
      PROCEDURE CANCEL_FAV_V2(
	IP_USER_ID IN NVARCHAR2,
	IP_RECORD_ID IN NUMBER,
	IP_TYPE_ID IN NUMBER,
	OP_OP_CODE OUT NUMBER,
	OP_MSG	   OUT NVARCHAR2
);
END COMMON_USER_FAV_tapi;

/
--------------------------------------------------------
--  DDL for Package COMMON_USER_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."COMMON_USER_TAPI" 
IS
type COMMON_USER_tapi_rec
IS
  record
  (
    LOGIN_ID COMMON_USER.LOGIN_ID%type ,
    REGSTATUS COMMON_USER.REGSTATUS%type ,
    CHANNEL_ID COMMON_USER.CHANNEL_ID%type ,
    GROUPID COMMON_USER.GROUPID%type ,
    USER_APP_ID COMMON_USER.USER_APP_ID%type ,
    USER_TYPE COMMON_USER.USER_TYPE%type ,
    STATUS COMMON_USER.STATUS%type ,
    ENCRYPTPASSWORD COMMON_USER.ENCRYPTPASSWORD%type ,
    NICKNAME COMMON_USER.NICKNAME%type ,
    USER_MOBILE_TYPE COMMON_USER.USER_MOBILE_TYPE%type ,
    ALLOWADMINCP COMMON_USER.ALLOWADMINCP%type ,
    RECORD_ID COMMON_USER.RECORD_ID%type ,
    IDNUMBER COMMON_USER.IDNUMBER%type ,
    USER_TAGS COMMON_USER.USER_TAGS%type ,
    NEWPROMPT COMMON_USER.NEWPROMPT%type ,
    NEWPM COMMON_USER.NEWPM%type ,
    LASTTIME COMMON_USER.LASTTIME%type );
type COMMON_USER_tapi_tab
IS
  TABLE OF COMMON_USER_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_LOGIN_ID         IN COMMON_USER.LOGIN_ID%type ,
      p_REGSTATUS        IN COMMON_USER.REGSTATUS%type DEFAULT NULL ,
      p_CHANNEL_ID       IN COMMON_USER.CHANNEL_ID%type DEFAULT NULL ,
      p_GROUPID          IN COMMON_USER.GROUPID%type DEFAULT NULL ,
      p_USER_APP_ID      IN COMMON_USER.USER_APP_ID%type DEFAULT NULL ,
      p_USER_TYPE        IN COMMON_USER.USER_TYPE%type DEFAULT NULL ,
      p_STATUS           IN COMMON_USER.STATUS%type DEFAULT NULL ,
      p_ENCRYPTPASSWORD  IN COMMON_USER.ENCRYPTPASSWORD%type DEFAULT NULL ,
      p_NICKNAME         IN COMMON_USER.NICKNAME%type DEFAULT NULL ,
      p_USER_MOBILE_TYPE IN COMMON_USER.USER_MOBILE_TYPE%type DEFAULT NULL ,
      p_ALLOWADMINCP     IN COMMON_USER.ALLOWADMINCP%type DEFAULT NULL ,
      p_RECORD_ID        IN COMMON_USER.RECORD_ID%type ,
      p_IDNUMBER         IN COMMON_USER.IDNUMBER%type DEFAULT NULL ,
      p_USER_TAGS        IN COMMON_USER.USER_TAGS%type DEFAULT NULL ,
      p_NEWPROMPT        IN COMMON_USER.NEWPROMPT%type DEFAULT NULL ,
      p_NEWPM            IN COMMON_USER.NEWPM%type DEFAULT NULL ,
      p_LASTTIME         IN COMMON_USER.LASTTIME%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_LOGIN_ID         IN COMMON_USER.LOGIN_ID%type ,
      p_REGSTATUS        IN COMMON_USER.REGSTATUS%type DEFAULT NULL ,
      p_CHANNEL_ID       IN COMMON_USER.CHANNEL_ID%type DEFAULT NULL ,
      p_GROUPID          IN COMMON_USER.GROUPID%type DEFAULT NULL ,
      p_USER_APP_ID      IN COMMON_USER.USER_APP_ID%type DEFAULT NULL ,
      p_USER_TYPE        IN COMMON_USER.USER_TYPE%type DEFAULT NULL ,
      p_STATUS           IN COMMON_USER.STATUS%type DEFAULT NULL ,
      p_ENCRYPTPASSWORD  IN COMMON_USER.ENCRYPTPASSWORD%type DEFAULT NULL ,
      p_NICKNAME         IN COMMON_USER.NICKNAME%type DEFAULT NULL ,
      p_USER_MOBILE_TYPE IN COMMON_USER.USER_MOBILE_TYPE%type DEFAULT NULL ,
      p_ALLOWADMINCP     IN COMMON_USER.ALLOWADMINCP%type DEFAULT NULL ,
      p_RECORD_ID        IN COMMON_USER.RECORD_ID%type ,
      p_IDNUMBER         IN COMMON_USER.IDNUMBER%type DEFAULT NULL ,
      p_USER_TAGS        IN COMMON_USER.USER_TAGS%type DEFAULT NULL ,
      p_NEWPROMPT        IN COMMON_USER.NEWPROMPT%type DEFAULT NULL ,
      p_NEWPM            IN COMMON_USER.NEWPM%type DEFAULT NULL ,
      p_LASTTIME         IN COMMON_USER.LASTTIME%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_LOGIN_ID IN COMMON_USER.LOGIN_ID%type );
      
      PROCEDURE REG(
  username in COMMON_USER.LOGIN_ID%type,
  idcard in varchar2,
  reg_ip in varchar2,
  last_ip in varchar2,
  pwd in  COMMON_USER.ENCRYPTPASSWORD%type,
  ip_nickname in COMMON_USER.NICKNAME%type,
  OP_USER OUT SYS_REFCURSOR,
  OP_INS_CODE OUT NUMBER 
);

   PROCEDURE login(
    username in COMMON_USER.LOGIN_ID%type,
     
   pwd in  COMMON_USER.ENCRYPTPASSWORD%type,
   last_ip in varchar2,
   OP_USER OUT SYS_REFCURSOR,
  OP_OP_CODE OUT NUMBER 
);

---生成推送TAG
  PROCEDURE GEN_TAGS(
    username in COMMON_USER.LOGIN_ID%type
  );
  
  PROCEDURE CA_QUERY_USER(
	IP_KEYWORDS IN NVARCHAR2,
	IP_USER_ID IN COMMON_USER.LOGIN_ID%TYPE,
	
	OP_SEL_RESULT OUT SYS_REFCURSOR,
	OP_OP_CODE OUT NUMBER
);
PROCEDURE QUERY_FELLOW(
	IP_AREACODE IN VARCHAR2,
	IP_USER_ID IN COMMON_USER.LOGIN_ID%TYPE,
	OP_SEL_RESULT OUT SYS_REFCURSOR,
	OP_OP_CODE OUT NUMBER
);
END COMMON_USER_tapi;

/
--------------------------------------------------------
--  DDL for Package DBMS_REPCAT_AUTH
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."DBMS_REPCAT_AUTH" wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9
bf d6
0cfc6e4Sm6mfaMYwsbW2JygBepcwg/BKmJ4VZy/pO06UXsVUMejsissTcGWYR4qeK4TPqfDK
q7UPH+SmKP6nW9zmxMZnuK1VDzM0Iv9O4E4SvvsnHWn+EPF9hR+oBFe3fEro6RQ5R5Ejd1nr
e+fAK010dExf76gg/c6ZB3YxGPHDOqkGI4lV6HNsd57gKLwTd0bxan5UwJriIpt7Vjc=


/
--------------------------------------------------------
--  DDL for Package JSON_AC
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."JSON_AC" as
  --json type methods

  procedure object_remove(p_self in out nocopy json, pair_name varchar2);
  procedure object_put(p_self in out nocopy json, pair_name varchar2, pair_value json_value, position pls_integer default null);
  procedure object_put(p_self in out nocopy json, pair_name varchar2, pair_value varchar2, position pls_integer default null);
  procedure object_put(p_self in out nocopy json, pair_name varchar2, pair_value number, position pls_integer default null);
  procedure object_put(p_self in out nocopy json, pair_name varchar2, pair_value boolean, position pls_integer default null);
  procedure object_check_duplicate(p_self in out nocopy json, v_set boolean);
  procedure object_remove_duplicates(p_self in out nocopy json);

  procedure object_put(p_self in out nocopy json, pair_name varchar2, pair_value json, position pls_integer default null);
  procedure object_put(p_self in out nocopy json, pair_name varchar2, pair_value json_list, position pls_integer default null);

  function object_count(p_self in json) return number;
  function object_get(p_self in json, pair_name varchar2) return json_value;
  function object_get(p_self in json, position pls_integer) return json_value;
  function object_index_of(p_self in json, pair_name varchar2) return number;
  function object_exist(p_self in json, pair_name varchar2) return boolean;

  function object_to_char(p_self in json, spaces boolean default true, chars_per_line number default 0) return varchar2;
  procedure object_to_clob(p_self in json, buf in out nocopy clob, spaces boolean default false, chars_per_line number default 0, erase_clob boolean default true);
  procedure object_print(p_self in json, spaces boolean default true, chars_per_line number default 8192, jsonp varchar2 default null);
  procedure object_htp(p_self in json, spaces boolean default false, chars_per_line number default 0, jsonp varchar2 default null);

  function object_to_json_value(p_self in json) return json_value;
  function object_path(p_self in json, json_path varchar2, base number default 1) return json_value;

  procedure object_path_put(p_self in out nocopy json, json_path varchar2, elem json_value, base number default 1);
  procedure object_path_put(p_self in out nocopy json, json_path varchar2, elem varchar2  , base number default 1);
  procedure object_path_put(p_self in out nocopy json, json_path varchar2, elem number    , base number default 1);
  procedure object_path_put(p_self in out nocopy json, json_path varchar2, elem boolean   , base number default 1);
  procedure object_path_put(p_self in out nocopy json, json_path varchar2, elem json_list , base number default 1);
  procedure object_path_put(p_self in out nocopy json, json_path varchar2, elem json      , base number default 1);

  procedure object_path_remove(p_self in out nocopy json, json_path varchar2, base number default 1);

  function object_get_values(p_self in json) return json_list;
  function object_get_keys(p_self in json) return json_list;

  --json_list type methods
  procedure array_append(p_self in out nocopy json_list, elem json_value, position pls_integer default null);
  procedure array_append(p_self in out nocopy json_list, elem varchar2, position pls_integer default null);
  procedure array_append(p_self in out nocopy json_list, elem number, position pls_integer default null);
  procedure array_append(p_self in out nocopy json_list, elem boolean, position pls_integer default null);
  procedure array_append(p_self in out nocopy json_list, elem json_list, position pls_integer default null);

  procedure array_replace(p_self in out nocopy json_list, position pls_integer, elem json_value);
  procedure array_replace(p_self in out nocopy json_list, position pls_integer, elem varchar2);
  procedure array_replace(p_self in out nocopy json_list, position pls_integer, elem number);
  procedure array_replace(p_self in out nocopy json_list, position pls_integer, elem boolean);
  procedure array_replace(p_self in out nocopy json_list, position pls_integer, elem json_list);

  function array_count(p_self in json_list) return number;
  procedure array_remove(p_self in out nocopy json_list, position pls_integer);
  procedure array_remove_first(p_self in out nocopy json_list);
  procedure array_remove_last(p_self in out nocopy json_list);
  function array_get(p_self in json_list, position pls_integer) return json_value;
  function array_head(p_self in json_list) return json_value;
  function array_last(p_self in json_list) return json_value;
  function array_tail(p_self in json_list) return json_list;

  function array_to_char(p_self in json_list, spaces boolean default true, chars_per_line number default 0) return varchar2;
  procedure array_to_clob(p_self in json_list, buf in out nocopy clob, spaces boolean default false, chars_per_line number default 0, erase_clob boolean default true);
  procedure array_print(p_self in json_list, spaces boolean default true, chars_per_line number default 8192, jsonp varchar2 default null);
  procedure array_htp(p_self in json_list, spaces boolean default false, chars_per_line number default 0, jsonp varchar2 default null);

  function array_path(p_self in json_list, json_path varchar2, base number default 1) return json_value;
  procedure array_path_put(p_self in out nocopy json_list, json_path varchar2, elem json_value, base number default 1);
  procedure array_path_put(p_self in out nocopy json_list, json_path varchar2, elem varchar2  , base number default 1);
  procedure array_path_put(p_self in out nocopy json_list, json_path varchar2, elem number    , base number default 1);
  procedure array_path_put(p_self in out nocopy json_list, json_path varchar2, elem boolean   , base number default 1);
  procedure array_path_put(p_self in out nocopy json_list, json_path varchar2, elem json_list , base number default 1);

  procedure array_path_remove(p_self in out nocopy json_list, json_path varchar2, base number default 1);

  function array_to_json_value(p_self in json_list) return json_value;

  --json_value


  function jv_get_type(p_self in json_value) return varchar2;
  function jv_get_string(p_self in json_value, max_byte_size number default null, max_char_size number default null) return varchar2;
  procedure jv_get_string(p_self in json_value, buf in out nocopy clob);
  function jv_get_number(p_self in json_value) return number;
  function jv_get_bool(p_self in json_value) return boolean;
  function jv_get_null(p_self in json_value) return varchar2;

  function jv_is_object(p_self in json_value) return boolean;
  function jv_is_array(p_self in json_value) return boolean;
  function jv_is_string(p_self in json_value) return boolean;
  function jv_is_number(p_self in json_value) return boolean;
  function jv_is_bool(p_self in json_value) return boolean;
  function jv_is_null(p_self in json_value) return boolean;

  function jv_to_char(p_self in json_value, spaces boolean default true, chars_per_line number default 0) return varchar2;
  procedure jv_to_clob(p_self in json_value, buf in out nocopy clob, spaces boolean default false, chars_per_line number default 0, erase_clob boolean default true);
  procedure jv_print(p_self in json_value, spaces boolean default true, chars_per_line number default 8192, jsonp varchar2 default null);
  procedure jv_htp(p_self in json_value, spaces boolean default false, chars_per_line number default 0, jsonp varchar2 default null);

  function jv_value_of(p_self in json_value, max_byte_size number default null, max_char_size number default null) return varchar2;


end json_ac;

/
--------------------------------------------------------
--  DDL for Package JSON_DYN
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."JSON_DYN" authid current_user as
 /*
  Copyright (c) 2010 Jonas Krogsboell

  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files (the "Software"), to deal
  in the Software without restriction, including without limitation the rights
  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  copies of the Software, and to permit persons to whom the Software is
  furnished to do so, subject to the following conditions:

  The above copyright notice and this permission notice shall be included in
  all copies or substantial portions of the Software.

  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  THE SOFTWARE.
  */

  null_as_empty_string   boolean not null := true;  --varchar2
  include_dates          boolean not null := true;  --date
  include_clobs          boolean not null := true;
  include_blobs          boolean not null := false;

  /* list with objects */
  function executeList(stmt varchar2, bindvar json default null, cur_num number default null) return json_list;

  /* object with lists */
  function executeObject(stmt varchar2, bindvar json default null, cur_num number default null) return json;


  /* usage example:
   * declare
   *   res json_list;
   * begin
   *   res := json_dyn.executeList(
   *            'select :bindme as one, :lala as two from dual where dummy in :arraybind',
   *            json('{bindme:"4", lala:123, arraybind:[1,2,3,"X"]}')
   *          );
   *   res.print;
   * end;
   */

/* --11g functions
  function executeList(stmt in out sys_refcursor) return json_list;
  function executeObject(stmt in out sys_refcursor) return json;
*/
end json_dyn;

/
--------------------------------------------------------
--  DDL for Package JSON_EXT
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."JSON_EXT" as
  /*
  Copyright (c) 2009 Jonas Krogsboell

  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files (the "Software"), to deal
  in the Software without restriction, including without limitation the rights
  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  copies of the Software, and to permit persons to whom the Software is
  furnished to do so, subject to the following conditions:

  The above copyright notice and this permission notice shall be included in
  all copies or substantial portions of the Software.

  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  THE SOFTWARE.
  */

  /* This package contains extra methods to lookup types and
     an easy way of adding date values in json - without changing the structure */
  function parsePath(json_path varchar2, base number default 1) return json_list;

  --JSON Path getters
  function get_json_value(obj json, v_path varchar2, base number default 1) return json_value;
  function get_string(obj json, path varchar2,       base number default 1) return varchar2;
  function get_number(obj json, path varchar2,       base number default 1) return number;
  function get_json(obj json, path varchar2,         base number default 1) return json;
  function get_json_list(obj json, path varchar2,    base number default 1) return json_list;
  function get_bool(obj json, path varchar2,         base number default 1) return boolean;

  --JSON Path putters
  procedure put(obj in out nocopy json, path varchar2, elem varchar2,   base number default 1);
  procedure put(obj in out nocopy json, path varchar2, elem number,     base number default 1);
  procedure put(obj in out nocopy json, path varchar2, elem json,       base number default 1);
  procedure put(obj in out nocopy json, path varchar2, elem json_list,  base number default 1);
  procedure put(obj in out nocopy json, path varchar2, elem boolean,    base number default 1);
  procedure put(obj in out nocopy json, path varchar2, elem json_value, base number default 1);

  procedure remove(obj in out nocopy json, path varchar2, base number default 1);

  --Pretty print with JSON Path - obsolete in 0.9.4 - obj.path(v_path).(to_char,print,htp)
  function pp(obj json, v_path varchar2) return varchar2;
  procedure pp(obj json, v_path varchar2); --using dbms_output.put_line
  procedure pp_htp(obj json, v_path varchar2); --using htp.print

  --extra function checks if number has no fraction
  function is_integer(v json_value) return boolean;

  format_string varchar2(30 char) := 'yyyy-mm-dd hh24:mi:ss';
  --extension enables json to store dates without comprimising the implementation
  function to_json_value(d date) return json_value;
  --notice that a date type in json is also a varchar2
  function is_date(v json_value) return boolean;
  --convertion is needed to extract dates
  --(json_ext.to_date will not work along with the normal to_date function - any fix will be appreciated)
  function to_date2(v json_value) return date;
  --JSON Path with date
  function get_date(obj json, path varchar2, base number default 1) return date;
  procedure put(obj in out nocopy json, path varchar2, elem date, base number default 1);

  --experimental support of binary data with base64
  function base64(binarydata blob) return json_list;
  function base64(l json_list) return blob;

  function encode(binarydata blob) return json_value;
  function decode(v json_value) return blob;

end json_ext;

/
--------------------------------------------------------
--  DDL for Package JSON_HELPER
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."JSON_HELPER" as
  /* Example:
  set serveroutput on;
  declare
    v_a json;
    v_b json;
  begin
    v_a := json('{a:1, b:{a:null}, e:false}');
    v_b := json('{c:3, e:{}, b:{b:2}}');
    json_helper.merge(v_a, v_b).print(false);
  end;
  --
  {"a":1,"b":{"a":null,"b":2},"e":{},"c":3}
  */
  -- Recursive merge
  -- Courtesy of Matt Nolan - edited by Jonas Krogsb縧l
  function merge( p_a_json json, p_b_json json) return json;

  -- Join two lists
  -- json_helper.join(json_list('[1,2,3]'),json_list('[4,5,6]')) -> [1,2,3,4,5,6]
  function join( p_a_list json_list, p_b_list json_list) return json_list;

  -- keep only specific keys in json object
  -- json_helper.keep(json('{a:1,b:2,c:3,d:4,e:5,f:6}'),json_list('["a","f","c"]')) -> {"a":1,"f":6,"c":3}
  function keep( p_json json, p_keys json_list) return json;

  -- remove specific keys in json object
  -- json_helper.remove(json('{a:1,b:2,c:3,d:4,e:5,f:6}'),json_list('["a","f","c"]')) -> {"b":2,"d":4,"e":5}
  function remove( p_json json, p_keys json_list) return json;

  --equals
  function equals(p_v1 json_value, p_v2 json_value, exact boolean default true) return boolean;
  function equals(p_v1 json_value, p_v2 json, exact boolean default true) return boolean;
  function equals(p_v1 json_value, p_v2 json_list, exact boolean default true) return boolean;
  function equals(p_v1 json_value, p_v2 number) return boolean;
  function equals(p_v1 json_value, p_v2 varchar2) return boolean;
  function equals(p_v1 json_value, p_v2 boolean) return boolean;
  function equals(p_v1 json_value, p_v2 clob) return boolean;
  function equals(p_v1 json, p_v2 json, exact boolean default true) return boolean;
  function equals(p_v1 json_list, p_v2 json_list, exact boolean default true) return boolean;

  --contains json, json_value
  --contains json_list, json_value
  function contains(p_v1 json, p_v2 json_value, exact boolean default false) return boolean;
  function contains(p_v1 json, p_v2 json, exact boolean default false) return boolean;
  function contains(p_v1 json, p_v2 json_list, exact boolean default false) return boolean;
  function contains(p_v1 json, p_v2 number, exact boolean default false) return boolean;
  function contains(p_v1 json, p_v2 varchar2, exact boolean default false) return boolean;
  function contains(p_v1 json, p_v2 boolean, exact boolean default false) return boolean;
  function contains(p_v1 json, p_v2 clob, exact boolean default false) return boolean;

  function contains(p_v1 json_list, p_v2 json_value, exact boolean default false) return boolean;
  function contains(p_v1 json_list, p_v2 json, exact boolean default false) return boolean;
  function contains(p_v1 json_list, p_v2 json_list, exact boolean default false) return boolean;
  function contains(p_v1 json_list, p_v2 number, exact boolean default false) return boolean;
  function contains(p_v1 json_list, p_v2 varchar2, exact boolean default false) return boolean;
  function contains(p_v1 json_list, p_v2 boolean, exact boolean default false) return boolean;
  function contains(p_v1 json_list, p_v2 clob, exact boolean default false) return boolean;

end json_helper;

/
--------------------------------------------------------
--  DDL for Package JSON_ML
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."JSON_ML" as
  /*
  Copyright (c) 2010 Jonas Krogsboell

  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files (the "Software"), to deal
  in the Software without restriction, including without limitation the rights
  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  copies of the Software, and to permit persons to whom the Software is
  furnished to do so, subject to the following conditions:

  The above copyright notice and this permission notice shall be included in
  all copies or substantial portions of the Software.

  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  THE SOFTWARE.
  */

  /* This package contains extra methods to lookup types and
     an easy way of adding date values in json - without changing the structure */

  jsonml_stylesheet xmltype := null;

  function xml2json(xml in xmltype) return json_list;
  function xmlstr2json(xmlstr in varchar2) return json_list;

end json_ml;

/
--------------------------------------------------------
--  DDL for Package JSON_PARSER
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."JSON_PARSER" as
  /*
  Copyright (c) 2010 Jonas Krogsboell

  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files (the "Software"), to deal
  in the Software without restriction, including without limitation the rights
  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  copies of the Software, and to permit persons to whom the Software is
  furnished to do so, subject to the following conditions:

  The above copyright notice and this permission notice shall be included in
  all copies or substantial portions of the Software.

  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  THE SOFTWARE.
  */
  /* scanner tokens:
    '{', '}', ',', ':', '[', ']', STRING, NUMBER, TRUE, FALSE, NULL
  */
  type rToken IS RECORD (
    type_name VARCHAR2(7),
    line PLS_INTEGER,
    col PLS_INTEGER,
    data VARCHAR2(32767),
    data_overflow clob); -- max_string_size

  type lTokens is table of rToken index by pls_integer;
  type json_src is record (len number, offset number, src varchar2(32767), s_clob clob);

  json_strict boolean not null := false;

  function next_char(indx number, s in out nocopy json_src) return varchar2;
  function next_char2(indx number, s in out nocopy json_src, amount number default 1) return varchar2;

  function prepareClob(buf in clob) return json_parser.json_src;
  function prepareVarchar2(buf in varchar2) return json_parser.json_src;
  function lexer(jsrc in out nocopy json_src) return lTokens;
  procedure print_token(t rToken);

  function parser(str varchar2) return json;
  function parse_list(str varchar2) return json_list;
  function parse_any(str varchar2) return json_value;
  function parser(str clob) return json;
  function parse_list(str clob) return json_list;
  function parse_any(str clob) return json_value;
  procedure remove_duplicates(obj in out nocopy json);
  function get_version return varchar2;

end json_parser;

/
--------------------------------------------------------
--  DDL for Package JSON_PRINTER
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."JSON_PRINTER" as
  /*
  Copyright (c) 2010 Jonas Krogsboell

  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files (the "Software"), to deal
  in the Software without restriction, including without limitation the rights
  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  copies of the Software, and to permit persons to whom the Software is
  furnished to do so, subject to the following conditions:

  The above copyright notice and this permission notice shall be included in
  all copies or substantial portions of the Software.

  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  THE SOFTWARE.
  */
  indent_string varchar2(10 char) := '  '; --chr(9); for tab
  newline_char varchar2(2 char)   := chr(13)||chr(10); -- Windows style
  --newline_char varchar2(2) := chr(10); -- Mac style
  --newline_char varchar2(2) := chr(13); -- Linux style
  ascii_output boolean    not null := true;
  escape_solidus boolean  not null := false;

  function pretty_print(obj json, spaces boolean default true, line_length number default 0) return varchar2;
  function pretty_print_list(obj json_list, spaces boolean default true, line_length number default 0) return varchar2;
  function pretty_print_any(json_part json_value, spaces boolean default true, line_length number default 0) return varchar2;
  procedure pretty_print(obj json, spaces boolean default true, buf in out nocopy clob, line_length number default 0, erase_clob boolean default true);
  procedure pretty_print_list(obj json_list, spaces boolean default true, buf in out nocopy clob, line_length number default 0, erase_clob boolean default true);
  procedure pretty_print_any(json_part json_value, spaces boolean default true, buf in out nocopy clob, line_length number default 0, erase_clob boolean default true);

  procedure dbms_output_clob(my_clob clob, delim varchar2, jsonp varchar2 default null);
  procedure htp_output_clob(my_clob clob, jsonp varchar2 default null);
end json_printer;

/
--------------------------------------------------------
--  DDL for Package JSON_UTIL_PKG
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."JSON_UTIL_PKG" authid current_user as

  /*

  Purpose:    JSON utilities for PL/SQL
  see http://ora-00001.blogspot.com/

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created
  JKR     01.05.2010  Edited to fit in PL/JSON
  JKR     19.01.2011  Newest stylesheet + bugfix handling

  */

  -- generate JSON from REF Cursor
  function ref_cursor_to_json (p_ref_cursor in sys_refcursor,
                               p_max_rows in number := null,
                               p_skip_rows in number := null) return json_list;

  -- generate JSON from SQL statement
  function sql_to_json (p_sql in varchar2,
                        p_max_rows in number := null,
                        p_skip_rows in number := null) return json_list;


end json_util_pkg;

/
--------------------------------------------------------
--  DDL for Package JSON_XML
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."JSON_XML" as
  /*
  Copyright (c) 2010 Jonas Krogsboell

  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files (the "Software"), to deal
  in the Software without restriction, including without limitation the rights
  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  copies of the Software, and to permit persons to whom the Software is
  furnished to do so, subject to the following conditions:

  The above copyright notice and this permission notice shall be included in
  all copies or substantial portions of the Software.

  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  THE SOFTWARE.
  */

  /*
  declare
    obj json := json('{a:1,b:[2,3,4],c:true}');
    x xmltype;
  begin
    obj.print;
    x := json_xml.json_to_xml(obj);
    dbms_output.put_line(x.getclobval());
  end;
  */

  function json_to_xml(obj json, tagname varchar2 default 'root') return xmltype;

end json_xml;

/
--------------------------------------------------------
--  DDL for Package MOBILE_TEACHER_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."MOBILE_TEACHER_TAPI" 
IS
type MOBILE_TEACHER_tapi_rec
IS
  record
  (
    MAJOR MOBILE_TEACHER.MAJOR%type ,
    TEACHERNAME MOBILE_TEACHER.TEACHERNAME%type ,
    RECORD_ID MOBILE_TEACHER.RECORD_ID%type ,
    GENDER MOBILE_TEACHER.GENDER%type ,
    TEACHERNO MOBILE_TEACHER.TEACHERNO%type ,
    LESSON MOBILE_TEACHER.LESSON%type );
type MOBILE_TEACHER_tapi_tab
IS
  TABLE OF MOBILE_TEACHER_tapi_rec;
  -- insert
  PROCEDURE ins(
      p_MAJOR       IN MOBILE_TEACHER.MAJOR%type DEFAULT NULL ,
      p_TEACHERNAME IN MOBILE_TEACHER.TEACHERNAME%type DEFAULT NULL ,
      p_RECORD_ID   IN MOBILE_TEACHER.RECORD_ID%type ,
      p_GENDER      IN MOBILE_TEACHER.GENDER%type DEFAULT NULL ,
      p_TEACHERNO   IN MOBILE_TEACHER.TEACHERNO%type DEFAULT NULL ,
      p_LESSON      IN MOBILE_TEACHER.LESSON%type DEFAULT NULL );
  -- update
  PROCEDURE upd(
      p_MAJOR       IN MOBILE_TEACHER.MAJOR%type DEFAULT NULL ,
      p_TEACHERNAME IN MOBILE_TEACHER.TEACHERNAME%type DEFAULT NULL ,
      p_RECORD_ID   IN MOBILE_TEACHER.RECORD_ID%type ,
      p_GENDER      IN MOBILE_TEACHER.GENDER%type DEFAULT NULL ,
      p_TEACHERNO   IN MOBILE_TEACHER.TEACHERNO%type DEFAULT NULL ,
      p_LESSON      IN MOBILE_TEACHER.LESSON%type DEFAULT NULL );
  -- delete
  PROCEDURE del(
      p_RECORD_ID IN MOBILE_TEACHER.RECORD_ID%type );
END MOBILE_TEACHER_tapi;

/
--------------------------------------------------------
--  DDL for Package SCHOOL_NOTIFICATION_TAPI
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYSTEM"."SCHOOL_NOTIFICATION_TAPI" 
IS

  -- insert
  PROCEDURE ins(
      p_NOTIFICATIONCONTENT       IN MOBILE_SCHOOL_NOTIFICATION.NOTIFICATIONCONTENT%type DEFAULT NULL ,
      p_NOFIFICATION_TITLE        IN MOBILE_SCHOOL_NOTIFICATION.NOFIFICATION_TITLE%type DEFAULT NULL ,
      p_AUTHORID                  IN MOBILE_SCHOOL_NOTIFICATION.AUTHORID%type DEFAULT NULL ,
      p_NOTIFICATION_TYPE_ID      IN MOBILE_SCHOOL_NOTIFICATION.NOTIFICATION_TYPE_ID%type DEFAULT NULL ,
      P_REFURL                    IN MOBILE_SCHOOL_NOTIFICATION.REFURL%TYPE DEFAULT '',
      p_notified_user in number,
      op_op_code out number
   
      );
  -- update
  PROCEDURE upd(
      p_NOTIFICATIONCONTENT       IN MOBILE_SCHOOL_NOTIFICATION.NOTIFICATIONCONTENT%type DEFAULT NULL ,
      p_NOTIFICATION_PUBLISH_DATE IN MOBILE_SCHOOL_NOTIFICATION.NOTIFICATION_PUBLISH_DATE%type DEFAULT NULL ,
      p_NOFIFICATION_TITLE        IN MOBILE_SCHOOL_NOTIFICATION.NOFIFICATION_TITLE%type DEFAULT NULL ,
      p_AUTHORID                  IN MOBILE_SCHOOL_NOTIFICATION.AUTHORID%type DEFAULT NULL ,
      p_NOTIFICATION_TYPE_ID      IN MOBILE_SCHOOL_NOTIFICATION.NOTIFICATION_TYPE_ID%type DEFAULT NULL ,
      p_NOTIFICATION_ID           IN MOBILE_SCHOOL_NOTIFICATION.NOTIFICATION_ID%type );
  -- delete
  PROCEDURE del(
      p_NOTIFICATION_ID IN MOBILE_SCHOOL_NOTIFICATION.NOTIFICATION_ID%type );
END SCHOOL_NOTIFICATION_tapi;

/
